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

5

2

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

Posted 2016-01-15T15:07:37.513

Reputation: 357

FWIW, I used the Feedback feature of Office 2016 to request this feature. Maybe others can do the same to help drive change :) – Dolan Antenucci – 2016-01-15T15:12:51.227

Under Windows, if you set it at the OS level, it applies as default to Excel too; I don't know for Mac. - If you don't want this for your whole system, you can add a format of the type 'date' and apply it to all places where you use dates; that is not changing the default, though. – Aganju – 2016-01-16T02:20:46.657

Answers

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

Posted 2016-01-15T15:07:37.513

Reputation: 357

Asked: 2016-01-15T15:07:37.513

Viewed: 11 012 times

Active: 2016-01-19T12:25:20.527