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.
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.
Everything should work as expected and even the advanced settings showed the correct values.
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.
defaults read NSGlobalDomain AppleLocale
After that, I switched it to my correct locale, which is ‘de_AT’ using the following command.
defaults write NSGlobalDomain AppleLocale de_AT
Once I opened Excel again and checked the date values everything works now as expected. Even the date values were corrected automatically.
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.