The many ways to set UK Locale in SharePoint

So you have a UK tenant and want to default from US Locale; unfortunately, there isn’t a default option. There is the default time zone, under Site Creation in Modern administration centre, but this does not include Locale. Those not in the US, you may find that your OneDrive and SharePoint sites are created with the US Locale, and thus you are subject to US Date Time formats. For UK tenants at least, you may want to find a way to automate or change this setting at scale. I could not find any way to set a tenant default - if you do please ping in the comments, but there are options for automating this with a variety of approaches.

Good news is it’s relatively easy to change the setting, you can in the user interface, PowerShell, Site Scripts. The bad news is that once you have blanket set the tenant, this is short-lived as new sites are created, this will need to be re-applied.

Setting the Locale using the User Interface

My Account

Under My Account (microsoft.com) settings, according to Microsoft support pages, this setting is a reference point for the preference for many Office 365 components. In the tenant, I use, this does not appear to affect the mentioned components. Understandably, the user interface does show “Preview” so it may not yet be fully rolled out to my tenant, bug with the feature or the support article does not align to the behavior. It is worth setting this anyway, if Microsoft fixes this issue then, it should, in theory, start to apply to the interface for you.

Settings under https://myaccount.microsoft.com/settingsandprivacy/language

Office 365 Profile

In Office 365, you can navigate to your Office 365 profile which takes you to Delve profile screen with the option to update your profile.

Video Clip on changing the regional settings in My Office 365 Profile

From my testing on the test environment, the date formats do update for the user preference, where the DateTime columns are set to standard mode, friendly names appear to be unaffected by this setting and show as “Month Day” format. Excel online doesn’t refer to these settings either and still uses the site settings as the default regional settings - you have the option to change this within the Excel file under File > Options > Regional Format Settings to set this to the region you prefer.

SharePoint Home

This is set on the root site collection under Site Settings > Regional Settings > Locale. Setting the setting here will update the SharePoint landing page.

OneDrive

You may think that this may not be necessary to fix this in OneDrive, there is one account per user and potentially could be seen as a minor thing. Until you use the Office Web Apps, such as Excel Online, which uses the site locale settings to use for default date formats (this also applies to files in SharePoint). You may think well having the desktop apps this is all ok, unless you use F3 or E1 licenses that do not include desktop apps as part of the license, only the online versions.

Excel Online vs Office 365 Excel Desktop app

Go to your OneDrive Site, Cog > OneDrive Settings > More Settings > Regional Settings or follow this small video below:

A short video on how to set locale settings in OneDrive.

After applying the setting, the Office apps will use the UK Locale setting, I had noticed that after setting this, it didn’t update with the UK date in the list view formatting.

SharePoint Site

Setting the SharePoint site is relatively straightforward, see video below, or go to Cog > Site Information > Site Settings > Regional Settings > Locale to change the value

Example clip of changing the setting

After this is applied, the site loads as expected. Ok, this is one site done, but what if you have thousands of sites, OneDrive accounts and what happens when new sites get created, how can we keep on top of this?

How can this be set automatically?

Unrealizing for quite some time, most of my scripts include the setting for locale one way or another, and I thought id share some of these to help you on your way to automate setting sites in bulk and use some automation options.

Site Designs

Site Designs is a lightweight provisioning solution that allows you to apply configuration options consistently to the site as they get created or on-demand. You can set the default site script to run when each site is created; however, not all site types are covered:

List of templates that can run Site Designs

{
    "$schema": "schema.json",
    "actions": [
        {
            "verb": "setRegionalSettings",
            "timeZone":2,
            "locale":2057,
            "sortOrder":25,
            "hourFormat":"24"
        }
    ],
    "bindata": {},
    "version": 1
}

Refer to this guidance for details: Site design JSON schema | Microsoft Docs and to install the script to you tenant this guidance will show you how to apply this: Get started creating SharePoint site designs and site scripts | Microsoft Docs

PnP PowerShell (Classic)

The locale on a site can be set using PnP PowerShell Classic (might work on latest one, just haven’t tested it yet), running a few example commands:

Connect-PnPOnline https://tenant.sharepoint.com/sites/yoursite
$LocaleId = 2057 # UK
$TimeZoneId = 2 # London

$web = Get-PnPWeb -Includes RegionalSettings,RegionalSettings.TimeZones
$timeZone = $web.RegionalSettings.TimeZones | Where-Object {$_.Id -eq $TimeZoneId}
$web.RegionalSettings.LocaleId = $LocaleId
$web.RegionalSettings.TimeZone = $timeZone
$web.Update()
Invoke-PnPQuery

You can bundle this up into a function or module so that you can run this in bulk against sites.

PnP Provisioning

The following PnP Provisioning template can apply the settings to a site, the relevant line is “<pnp:RegionalSettings />” that contains the settings to apply to a site. See this example of how you can apply using the provisioning engine:

Provisioning Template:

<?xml version="1.0"?>
<pnp:Provisioning xmlns:pnp="http://schemas.dev.office.com/PnP/2020/02/ProvisioningSchema">
  <pnp:Preferences Generator="OfficeDevPnP.Core, Version=3.25.2009.1, Culture=neutral, PublicKeyToken=null" />
  <pnp:Templates ID="CONTAINER-TEMPLATE-53C4E3E3CBC6443A8CE0B1B25C211A6F">
    <pnp:ProvisioningTemplate ID="TEMPLATE-53C4E3E3CBC6443A8CE0B1B25C211A6F" Version="1" BaseSiteTemplate="STS#3" Scope="RootSite">
      <pnp:RegionalSettings AdjustHijriDays="0" AlternateCalendarType="None" CalendarType="Gregorian" Collation="25" FirstDayOfWeek="Monday" FirstWeekOfYear="0" LocaleId="2057" ShowWeeks="false" Time24="true" TimeZone="2" WorkDayEndHour="5:00PM" WorkDays="62" WorkDayStartHour="8:00AM" />
    </pnp:ProvisioningTemplate>
  </pnp:Templates>
</pnp:Provisioning>

Connect-PnPOnline https://tenant.sharepoint.com/sites/yoursite
Apply-PnPProvisioningTemplate -Path "UK-Locale-Example-Provisioning-Template.xml"

Azure Automation

You use Azure Automation to apply the change to the site. If you are planning to apply this to a large number of OneDrive sites, the App-Only method of connecting to SharePoint will provide access to all the OneDrive sites. This avoids having to set yourself as an administrator on each site, apply the change and remove after or provide your information governance teams with some reassurance that the engineer does not access the OneDrive sites given they are considered personal.

Using the App-only sample, that we contributed to the PnP PowerShell Repository: PnP-PowerShell/Samples/Connect.AzureAutomation at master · pnp/PnP-PowerShell (github.com) will get you started with setting up Azure Automation.

[CmdletBinding()]
Param
(
[string]$SiteUrl
)

# Refer to https://github.com/pnp/PnP-PowerShell/tree/master/Samples/Connect.AzureAutomation
# for setting up Azure Automation using an App Only connection to SharePoint Online

# Retrieves from the Azure Automation variables and certificate stores 
# the details for connecting to SharePoint Online
$azureAutomateCreds = Get-AutomationPSCredential -Name 'AzureAppCertPassword'
$appId = Get-AutomationVariable -Name "AppClientId"
$appAdTenant = Get-AutomationVariable -Name "AppAdTenant"
$appCert = Get-AutomationCertificate -Name "AzureAppCertificate"

# Site Template List
try {
    Write-Verbose "Running Script..."
   
    # Export the certificate and convert into base 64 string
    $base64Cert = [System.Convert]::ToBase64String($appCert.Export([System.Security.Cryptography.X509Certificates.X509ContentType]::Pkcs12))

    Write-Verbose "Connected to SharePoint Online Site"
   
    # Connect to the standard SharePoint Site
    $siteConn = Connect-PnPOnline -ClientId $appId -CertificateBase64Encoded $base64Cert `
        -CertificatePassword $azureAutomateCreds.Password `
        -Url $SiteUrl -Tenant $appAdTenant -ReturnConnection
    
    $LocaleId = 2057 # UK
    $TimeZoneId = 2 # London
    
    $web = Get-PnPWeb -Includes RegionalSettings,RegionalSettings.TimeZones -Connection $siteConn
    $timeZone = $web.RegionalSettings.TimeZones | Where-Object {$_.Id -eq $TimeZoneId}
    $web.RegionalSettings.LocaleId = $LocaleId
    $web.RegionalSettings.TimeZone = $timeZone
    $web.Update()
    Invoke-PnPQuery

}
catch {
    Write-Error "An error occurred: $($PSItem.ToString())"
}

The example runbook above applies to a single site, you could have a companion runbook that uses Get-PnPTenantSite -IncludeOneDriveSites cmdlet to get all the SharePoint and OneDrive sites with the switch, and call this runbook to apply this to all sites within your tenant. If you do run this script against all sites in your tenant, watch out for the 3 hour run limit for runbooks, refer to this for more information: Azure subscription limits and quotas - Azure Resource Manager | Microsoft Docs

Conclusion

There are many ways to solve this, all each has its advantages. Hopefully, this can help you determine the best approach for your scenarios. Whilst much of this article refers to UK locale, much of this can easily be changed for other locales for your region and avoid having your staff working with US date formats.

Update 7th Nov: Updated article based on feedback - Thank you! Keep posting comments if you spot anything I have missed.

Enjoy!