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.
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.