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.

Microsoft Teams & OneDrive - Fix issue with Excel date formats

Comments

More from this blog

Fixing 'There is no Functions runtime available' in Visual Studio

Check length (duration) of Microsoft Teams meetings

Enable '+' plus addressing in Exchange Online

Shortcut to turn off monitors on Windows 10

How to automatically backup a WordPress site to AWS S3

Set regional settings in SharePoint

Globally

Per site basis

Change the language in Teams

Change region in OneDrive for Business

Command Palette

Comments

More from this blog

Set regional settings in SharePoint

Globally

Per site basis

Change the language in Teams

Change region in OneDrive for Business