Mac – As a US user, how can I change the default date format in Excel 2016 (Mac) to YYYY-MM-DD

macmicrosoft excelmicrosoft-excel-2016

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>)

Best Answer

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.
Related Question