Excel formulations do not work in sharepoint and microsoft teams ?

Question

I copied an excel file to our "Microsoft Teams" group and I realized that the formulations do not work at Microsoft teams desktop application and SharePoint whether I open the file in browser or excel program although it was working when the file was in desktop. I tried every recommendations on the site but always calculations show "0" and the file does not indicate any error. When I clicked the cell which formulation in it and enter the "enter tab", then the formula calculate the number, but I have to do that in every opening and for every cells containing formulas. I use "countifs" and "sumifs" functions in my formulas. Is there any solution for that?

Answer 1

Hi Özgür,

Thanks for the detailed screenshots.

For the problem showing in the first screenshot:

The date format of the "data entry part" is based on the computer's Region settings. In Windows, click on the Start, just type "region", click on the pop-up Region settings, you may change the setting of the Region format to the United Kingdom.

*I also suggest you change the setting of the Country or region to the United Kingdom.

**You may also click the Change data formats button to change the detailed settings of the Regional format data.

For the problem of "date format showing differently in Microsoft Teams".

In Microsoft Teams, you may click on the user picture -> Settings, on the General tab, change the Language to the United Kingdom. Save and restart Teams and check the result.

After confirming the problems disappeared in both the Excel client and Microsoft Teams, check whether the third problem persists after clicking the Open in Desktop App button on the navigation on Microsoft Teams.

Besides, I would like to suggest you refer to this article: Change your personal language and region settings. The Locale setting affects the date formats of Excel workbooks stored in SharePoint Online (after clicking the three dots ...  -> Open in Browser on the upper right of the opening workbook screen in Microsoft Teams) and OneDrive for Business.

*The change may need up to 24-hours to take effect.

**I’m also testing to confirm that.

Hope this helps.

Just post back if you got any trouble during trying the suggestions. I also appreciate your patience as well. :)

Best Regards,

Hugo

Answer 2

Hi Hugo,

Thank you so much for your effort. I have realized that the main problem is date format.

I format the cells as "showing DD/MM/YYYY". When I try to enter a date such as 09/01 (for 9th of January) to the cell, the excel automatically assume this day as 1st of Sep (You may see the screenshot), I could not change the variation :(.

Answer 3

Hi Özgür,

May I know the original Date type? Per my tests, the Custom date format (the second type) may be affected by the original Date type (The first picture).

Reference: Format a date the way you want

Best Regards,

Hugo

Answer 4

Hi Hugo,

Thank you for your reply.

I would like to clarify the issue with screenshots. When I try to format date type as DD.MM.YYYY., at Microsoft Teams app or web browser (first picture), the format is reshaped at the cell  but in the data entry part (please look at fx section at below screenshot) it still same as MM.DD.YYYY. So it affects the formulation because workbook thinks days as months. And also when I open the workbook again at microsoft teams or web browser (2nd picture) the location is changed by itself (2nd picture).  When I open the workbook at desktop application, again all formats are changed (3rd picture). I couldn't fix :( 

Answer 5

Thank you so much. It is solved :)