Excel for Mac (Office 365) refuses to use regional settings from Mac OS 14.1

Question

I want my Mac language to be US English, as that is what I am used to. I live in Denmark, so my regional settings, like thousand and decimal separators, date formats etc. are set differently. Excel for Mac (latest version, 16.79.1) does not seem to respect the regional settings from my Mac. Instead, it uses the Language, which is US English. For example, if I enter "5/7" in a cell - Excel interprets it as May 7. I want it to interpret it according to my regional settings, so 5/7 should be interpreted as 5th of July. Is it a bug in Excel for Mac, that it sets regional settings, based on Language, rather than the regional settings chosen in the operating system?

Answer 1

With the macOS regional setting "English (US)" the system and Excel don't agree on how to read the environment locale, and then Excel uses its defaults instead. With regional setting set to "English (UK)" the system is OK, as you have experienced.

Referring to this post, https://forums.macrumors.com/threads/apple-releases-macos-sonoma-14-1.2408652/page-5?post=32666688#post-32666688, you can check your setting using the command

defaults read .GlobalPreferences AppleLocale

If the output is something messed up like en_US@rg=dkzzzz, you can override the setting with the command:

defaults write .GlobalPreferences AppleLocale en_DK

(assuming you want the English (US) - Danish combo indicated in your screenshot)

Answer 2

Thanks for the heads-up Hans. I did update the system and it lost the AppleLocale setting I'd made. I came back here to copy and paste the commands. I did so from my own post above, and noticed a small but important mistake in my post. I'd written:

write .GlobalPreferences AppleLocale en_DK

It should have been:

defaults write .GlobalPreferences AppleLocale en_DK

I edited and corrected my previous post.

Answer 3

Wow, thanks Hans!

I opened the Terminal App and copied and pasted this command:

defaults read .GlobalPreferences AppleLocale   

In return I got "en_DK", but that was because my Mac was set to English (UK). I deleted English (UK) from the top of the preferred languages list in System settings > Language & Region > Region for Internet Services and restarted my Mac for the change of primary language to take effect (as can be seen in a screen shot in my previous post, second on that list was English (US). After deletion of English (UK), English (US) was on top of the list).

Again, in the Terminal App I pasted this command:

defaults read .GlobalPreferences AppleLocale   

This time, in return I got "en_US@rg=dkzzzz".

On to the next command in the Terminal App:

defaults write .GlobalPreferences AppleLocale en_DK  

Just for good measure, I did another

defaults read .GlobalPreferences AppleLocale 

and got "en_DK" in return.

Then, I started Excel and punched in today's date as 25/11, in a cell that was formatted yyyy-mm-dd and it displayed correctly as 2023-11-25.

Once again, thank you very much, Hans! I would never have been able to figure that out on my own.

Answer 4

Hello Sola,

Thank you very much for trying to help - much appreciated!

1. There is nothing called "International" in the Excel Preferences in my Excel for Mac (v. 16.79.1 - as far as I know this is the latest version - I updated within the last few days). This is what is available (see screen shot):

As I understand it, the only way to make those kinds of changes in Excel for Mac, is to do it in the Mac operating system.

2. Everything is set as I want in the System Preferences of Mac OS (14.1.1 - just updated yesterday), except I have to use English (UK) as Primary Language - as a work-around to Excel not respecting my system level regional settings (see screen shot):

I've made the system changes while Excel wasn't running and I've restarted the computer (necessary for the change of system language to take effect).

3. I know that I can format cells in Excel to show dates however I choose, but that doesn't help during input. If, for example, I write 23/11 in a cell, Excel does not understand that this is a date, and if I write 5/7, Excel will wrongly interpret that as May 7 (instead of 5th of July), if the primary system language is set to English (US), that is - regardless of my regional settings in the operating system.

Answer 5

Nice work Hans. I had the same problem, but en_FI, ie. Finnish. Worked like a champ, fixed all my excel sheets. You are the man. Kiitos