10

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}.

  • 1
    My kingdom for a standardized way of dealing with dates. Oh, wait.... – Max Vernon May 3 '17 at 12:44
7

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

  • 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 Oct 23 '15 at 22:03
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".

  • 1
    This 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 Oct 21 '15 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.