Article
0 comment

How to apply your locale to Office for Mac

Today I recognised an annoying problem in Excel because a workbook in Office 2016 for Mac showed the wrong date and currency formats. I always got the English date format as value. Sure I was able to change the date values to match the German format convention, but that was just for display.

Excel showing wrong date format according to region settings

Excel showing wrong date format according to region settings

Even when I entered a correct german date, then it was not recognised as such. Excel identified the value only as text.

First I checked system preferences

In my case, I always use English as the default UI language but change the region setting to mine.

MacOS - My language and region settings

MacOS – My language and region settings

Everything should work as expected and even the advanced settings showed the correct values.

MacOS – Language and Region Settings avanced

So why is Excel then struggling with my system settings?

The problem and the solution

I seem that the configurations available via the user interface don’t have any effect on Excel.
It seems that this change happens in MacOS Sierra. Nearly any application follow now the settings of User Interface and not the parameter of the region anymore. Even Microsoft Office follow this pattern.
The good news is that what cannot adjust via the user interface can be modified through the terminal. The setting ‘AppleLocale’ store the value that application uses to identify the current locale and correlates to the language settings of the user interface.
In case you don’t like to change your system language to German you need to change the value of ‘AppleLocale’.
First I checked the current value using the following command to make sure the value is wrong.

AppleLocale – current settings

After that, I switched it to my correct locale, which is ‘de_AT’ using the following command.

AppleLocale – value changed in terminal

Once I opened Excel again and checked the date values everything works now as expected. Even the date values were corrected automatically.

Excel with correct local set

 

I must admit this is not a bug by Office. It seems to be a bummer in MacOS that the region and overall language won’t be set correctly. On the other hand, the regular user might just like to change the global setting of the OS and not have mixed language and region setup. Anyway, this hack helped me a lot to get it working as I would have expected.


Also published on Medium.