How to make date YYYY-MM-DD (ISO-8601) the default in Excel?

11

2

In Excel how can I make the ISO8601 date format, yyyy-mm-dd, the default? Windows locale is English(CA) or English(US). We're using Excel 2013, though a generic answer for as many versions as possible is preferred.

Using a custom format as in How do I enter dates in ISO 8601 date format (YYYY-MM-DD) in Excel (and have Excel recognize that format as a date value)? doesn't work, because it needs to be repeated for every new field. This is especially painful when working with CSV files.

[XKCD Public Service Announcement: Our different ways of writing dates as numbers can lead to online confusion. That's why in 1988 ISO set a global standard numeric date format. This is **the** correct way to write numeric data: 2013-02-27. The following formats is discouraged {insert list of everything else}.

matt wilkie

Posted 2015-10-20T18:16:35.667

Reputation: 4 147

1My kingdom for a standardized way of dealing with dates. Oh, wait.... – Max Vernon – 2017-05-03T12:44:07.030

Answers

8

For that you actually need to change the whole Windows date format. For that, go to:

Control Panel > Regional and Language Options > Change date, time or number formats

There, choose for Short date the yyyy-MM-dd format, and done!

Here's the proof it works. Even if you write another date format, Excel will automatically convert it to the system's default.

Excel date format changing

Keep in mind that this will also change the way Windows displays the date.

Windows date format set to yyyy-MM-dd

Source

Hewbot

Posted 2015-10-20T18:16:35.667

Reputation: 1 604

This is the right answer, in that it correctly answers what I asked for, but, in the end actually wrong for my use case. It turns out that what I actually need is the default-on-load to be dd-mm-yy and default-to-display & default-to-export to be yyyy-mm-dd. So heads up to others in this situation: you need to always import the file with text wizard and set the incoming date format. – matt wilkie – 2015-10-23T22:03:09.983

1

Set that format in windows regional settings. Excel will use system settings as default.

For that you will have to go to Control Panel, choose "Clock, Language, and Region", and then "Region and Language". Click the "Formats" tab, and then, in the Format list choose "Short Date" and from the drop down list choose the appropriate option "yyyy-MM-dd".

CFreitas

Posted 2015-10-20T18:16:35.667

Reputation: 482

1This answer, as it stands, isn't as easily understandable as we'd like. It would be a good idea to briefly explain how to do this so that this answer would be more useful. – bwDraco – 2015-10-21T11:30:24.793

Asked: 2015-10-20T18:16:35.667

Viewed: 5 862 times

Active: 2016-03-27T23:17:22.573