Set language and date format in Office 365 Online using PowerShell

How to set language and date format (e.g. English UK or English US) in Office 365 for Excel Online and Outlook Online using PowerShell

Excel Online shows a different date format from Excel Desktop App

In this example, a spreadsheet created in Excel desktop app with dates in English UK format (dd/mm/yy) incorrectly displays dates in English US format when uploaded to Teams and opened with Excel Online.

UK Date Format

When the spreadsheet is uploaded to Teams and opened in Excel online, the dates display in English US format mm/dd/yy.

English US date format

To fix this problem, we will use PowerShell to set the Office 365 language and date format for the user.

Change Office 365 language and date format using PowerShell

Language and region settings in Office 365 can be set using PowerShell Set-MsolUser PreferredLanguage.

Set-MsolUser PreferredLanguage uses a language ID to set the regional language and date format.

Example: Set preferred language to en-CA English (Canada)

Get-MsolUser -UserPrincipalName bender@planetexpress.live | Set-MsolUser -PreferredLanguage "en-CA"

Office 365 language ID list

Here is a quick list of commonly used Office 365 language IDs. For a complete list, see the reference link at the end of this article.

Get Office 365 language using PowerShell Get-MsolUser PreferredLanguage

# Connect to Azure Active Directory
Connect-MsolService

# Get-MsolUser PreferredLanguage
Get-MsolUser -UserPrincipalName bender@planetexpress.live | Select UserPrincipalName, PreferredLanguage 

# Get-MsolUser all properties 
Get-MsolUser -UserPrincipalName bender@planetexpress.live | fl 

Set Office 365 language using PowerShell Set-MsolUser PreferredLanguage

# Set preferred language to English UK
Get-MsolUser -UserPrincipalName bender@planetexpress.live | Set-MsolUser -PreferredLanguage "en-GB"

# Set preferred language to English US
Get-MsolUser -UserPrincipalName bender@planetexpress.live | Set-MsolUser -PreferredLanguage "en-US" 

Office 365 Usage Location

The usage location determines which Office 365 licenses and features are available based on the country.

Find the geo location of a mailbox

# Connect to Exchange online
# Get mailbox region
Get-Mailbox -Identity bender@planetexpress.live | Format-List Database,MailboxRegion* 

 Mailbox database is in the United Kingdom (GB)

Change Office 365 usage location using PowerShell Set-MsolUser -UsageLocation

# Get current usage location
Get-MsolUser -UserPrincipalName bender@planetexpress.live | Select UserPrincipalName, PreferredLanguage, UsageLocation

# Change usage location to United States US
Set-MsolUser -UserPrincipalName bender@planetexpress.live -UsageLocation US
 

Set Exchange Online language and date format using PowerShell Set-MailboxRegionalConfiguration

You can use Set-MailboxRegionalConfiguration to change the language, date format, time format, and time zone for Exchange online.

Get Exchange online mailbox regional settings

# Connect to Exchange Online PowerShell
# Get mailbox current regional configuration settings 
Get-Mailbox bender@planetexpress.live | Get-MailboxRegionalConfiguration  

Time formats

The default time format for English UK is HH:mm which is 24-hour clock format. To use AM/PM instead, change the time format to h:mm tt

Change mailbox regional configuration

# Change mailbox regional configuration to English UK
Get-Mailbox bender@planetexpress.live | Get-MailboxRegionalConfiguration  | Set-MailboxRegionalConfiguration -Language en-GB -DateFormat "dd/MM/yyyy" -TimeFormat "HH:mm" 

# Change mailbox regional configuration to English US
Get-Mailbox bender@planetexpress.live | Get-MailboxRegionalConfiguration  | Set-MailboxRegionalConfiguration -Language en-US -DateFormat "MM/dd/yyyy" -TimeFormat "HH:mm" 

# Change mailbox regional configuration to Spanish (Spain), Time zone Central European Time (CET) 
Get-Mailbox bender@planetexpress.live | Get-MailboxRegionalConfiguration  | Set-MailboxRegionalConfiguration -Language es-ES -DateFormat "MM/dd/yyyy" -TimeFormat "HH:mm" -TimeZone "C. Europe Standard Time"
 

Change language and date format in Excel Online

People can change the Regional format settings themselves in Office 365 Online.

Open Excel online
File - options - Regional Format Settings

Select your preferred regional format for Excel

Change language and date format in Outlook for the web

Outlook Online
Settings - View All Outlook Settings

General - Language and time

References:

How to set language and region settings for Office 365
https://docs.microsoft.com/en-US/office365/troubleshoot/access-management/set-language-and-region

Language, Location, and Language tags
Windows Language Code Identifier (LCID) Reference
5 Appendix A: Product Behavior
https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-lcid/a9eac961-e77d-41a6-90a5-ce1a8b0cdb9c

For lists of country and region names that are supported by various Windows operating system versions, see the Language, Location, and Language tag columns of the table in Appendix A: Product Behavior in [MS-LCID]: Windows Language Code Identifier (LCID) Reference.

Administering Exchange Online mailboxes in a multi-geo environment
https://docs.microsoft.com/en-us/microsoft-365/enterprise/administering-exchange-online-multi-geo

by Author