Microsoft Teams & OneDrive – Fix issue with Excel date formats

I work in a UK based company and we’ve recently been using Microsoft Teams more and more, which for the most part has been great. However, whenever someone edits an Excel workbook within the Teams web (or desktop) app, they realise that datetime values are in the US format (MM/DD/YYYY). This seems to happen even if they have previously set the regional settings to UK in SharePoint / Office 365. If you haven’t yet set the regional settings for the Office 365 group’s SharePoint site, then you should do that first. If you have then you can skip this step.

Set regional settings in SharePoint

By default all SharePoint sites created are set to United States time zone and locale. You can now set the default region for all new sites via the SharePoint admin center. This works if your users are not spread across the globe. If they are then you can either set the region on a site by site basis or use a script to provision sites and set the region based of the information provided by the requester.

Globally

Check out the post here on how to automatically set regional settings for all new sites.

Per site basis

  • Open the Teams app, select the team you want to manage
  • Go to Files tab and click Open in SharePoint
  • Select the Settings cog icon and click Site contents, then press Site settings
  • Under Site Administration, select Regional settings
  • Change the Time Zone and Locale to your region. By default it is United States for all sites.
  • Press OK to save your changes.
  • Quit and re-launch your Teams app and try to load an Excel file with region specific dates. If it is now working as expected then you probably don’t need to change the default language in Teams. If it is still incorrect then try the steps below.

If you have global users then this becomes a bit more fidly as you may need to ask the owner/creator of the group to manually update the regional settings to their desired value. In the past I’ve automated an email that gets sent after a new group is created that gives the user some information on how to use and renew the group, as well as how to do some default configuration (like setting region, adding members, etc…)

Change the language in Teams

  • Open the Teams desktop app or go to teams.microsoft.com
  • In the top right, click on your profile image
  • Select Settings
  • Under Language > App language determines the date and time format.
  • Select your language and region (example: English (United Kingdom))
  • If you re-open your Excel workbook the datetime should now be in the correct format (DD/MM/YYYY).

Change region in OneDrive for Business

If you commonly use Excel in a web browser and the files are stored in your own OneDrive then you may have issues with dates trying to be displayed in US format! Like everything else, the default is for the US market, luckily it’s easy to change.

Open OneDrive in a web browser

Click on the Settings (Cog) Icon at the top right

Select OneDrive settings under OneDrive header

Select More Settings under the left menu

Under Region and Language, select Regional settings

Under Region > Locale, change it to your Locale. In my case English (United Kingdom)

Press OK to save your changes.

6 thoughts on “Microsoft Teams & OneDrive – Fix issue with Excel date formats

  1. Hi there,

    This doesn’t seem to work anymore just before xmas i had a bunch of users reporting the date format has switched to US even though the settings in Windows, Teams, Web browsers are all set to NZ.

    It also seems to affect my PBI Reports which are embedded as a tab but display correct in the PBI desktop app and web browser.

    Is this fix still working for you?

    Thanks

    1. Hi, I’ve just created a new O365 group to test and it looks like it is working as expected. The only thing that had to be done was ensuring the Regional settings for the SharePoint site was correct (by default new sites are set to United States region/locale). Setting just the teams app language wasn’t enough. I’ve expanded the post with how to do that.

      1. Thanks for the prompt response. That worked perfectly for the excel files thanks again.

        Only issue i still have is the Power BI Report which is embedded as a tab is still showing as MM/DD/YYYY.

        I can confirm my Power BI Service settings for language are set to Default (Web Browser) and my default browser being chrome is set to New Zealand English. Opening the report from Teams using the Globe (Go to website) button it displays the dates correctly.

        I have also checked the report in IE and Edge and they both display correctly as well.

        Windows display language, country and region are all set to NZ (Including the admin locale)

        Also when i just insert the report url as a webpage in Teams it also shows up as MM/DD/YYYY

        Any other places you can think of checking?

        Thanks,

        1. Ah, looks like it’s more of a limitation/bug of Teams Desktop app on Windows. It’s built using Electron and that uses Chromium (github issue). Seems to be for any embedded web pages (like Power BI reports) it’s just using the default US date time format and ignoring what your local settings are. Sorry I couldn’t be of more help, Microsoft needs to fix this. There’s quite few user voice entries open like this one but none are progressing much.

          1. No worries, Thanks for all your help.

            Saw you added the global settings too for new sites which helps.

  2. This saves my life, a user asked me and I thought I could never fix this issue until I found this article. Thanks a million.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.