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 Ozgur,

I did some tests but cannot reproduce this problem, so I think there may have some problems with the Teams client or the formulas, and you may try the below suggestions and check the results.

Steps for your reference:

Create a new Excel workbook and use the example data listed in SUMIFS function and COUNTIFS function articles, drag the workbook to the Files tab in Microsoft Teams, click the workbook to open it from Teams and select the Open in Excel option to open it in the desktop client or select the Open in Browser option to open it from SharePoint Online.

Test workbook: https://1drv.ms/x/s!AnEYnmpvPKE6gS2rpG1ux4-6L5cv?e=SyOoxC

*You may directly download the test workbook and copy it to Teams to check the results.

Suggestion you may refer to:

1. On the Files tab, click the Open in SharePoint and upload the affected Excel workbook to SharePoint Online, check whether the issue persists after opening the workbook from Excel for the web.
2. If you can confirm there have some problems with the original workbook after the tests, you may double-check the formulas in the affected workbook with the suggestions listing in the related official articles.
3. If you still cannot find any solution, you may share the workbook here so that we can have a look at the Excel workbook and give you more suggestions.

* Refer to this article: Share OneDrive files and folders. To protect your privacy, we suggest you make a copy of the affected workbook, delete the irrelevant data and replace the remaining personal information with sample data, then upload the workbook to your personal OneDrive and put a personal OneDrive shared link when you reply.

We appreciate your understanding.

Best Regards,

Hugo

Answer 3

Hi Hugo,

I tried every solutions but still have problem. I think the "date type" is affecting formulations because the formulations works whether in browse or excel, not working at both of them. I have shared the file with you at below link if you have any solution on that.

Thanks,

Özgür

https://drive.google.com/open?id=1YlNdvHr2V4vSIG3wCDKxqSGKTgIYkbNu

Answer 4

Hi Özgür,

Thanks for the updates.

I've downloaded the workbook and I will get back to you once I can get some results.

Best Regards,

Hugo

Answer 5

Hi Özgür,

Thanks for your patience.

I think there are some problems with the dates referenced by the formulas in the Excel workbook.

In the DGA summary monthly worksheet, in the B6 cell, for example, change the 02.01.2020 and 02.01.2020 to 02/01/2020 and 02/01/2020, this gets the actual returned number of 8 and it also works in Microsoft Teams and SharePoint Online.

*Excel Client:

*Microsoft Teams/SharePoint Online:

During tests, I cannot reproduce the situation of "the formulations works whether in browse or excel, not working at both of them". After changing the format of the dates in the workbook, formulas begin to work in both the Excel client and the Microsoft Teams/SharePoint Online.

If the pictures are showing the expected results, you may have a try and check the outcomes. :)

Best Regards,

Hugo