6

Note: I see several questions (q1, q2) on this topic that describe how to accomplish this with the Windows version of Office, but I haven't found anything on Mac version of Office.

I'm a US user, and I want to change the default date format of Excel to YYYY-MM-DD so that if I open an Excel sheet or a CSV file with dates, it displays this format in the column. (In the case of a CSV file, if the format is already YYYY-MM-DD, I don't want it switch to the default format).

Alternatively, how can have this format show up as an option under Format Column -> Date -> US Dates? I end up having to switch my country to Belgium before I can find this option.

(<rant>@Microsoft -- I'm pretty sure in 2016, people in the US use the YYYY-MM-DD format; at least make it an option!</rant>)

Dolan Antenucci
  • 377

2 Answers2

6

It turns out that OS X's Language & Region "Short" date format controls how dates appear in Excel.

To modify how Excel formats ALL date columns by default to "YYYY-MM-DD":

  1. Go to OS X System Preferences -> Language & Region -> Advanced
  2. Change the "Short" date format by cutting-and-pasting the year to beginning
  3. Change the separator character between the year, month, and date to -'s.
  4. Use the month and day fields' dropdown feature to select the zero-filled version (e.g., "01" if your current month is January).
  5. Restart Excel for change to take affect.
Dolan Antenucci
  • 377
2

Office defaults to the default format of the system wide selected language.

One of the english languages, that has the ISO date format, is Germany. Note that you also get the default currency and other formatting options.

You can set it using a terminal command:

defaults write -app "Microsoft Excel" AppleLocale en_DE

Thanks to countries that care about standards, and thanks to a random reddit user.

MarZab
  • 121