17

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

Improve this question
1
  • 2
    My kingdom for a standardized way of dealing with dates. Oh, wait....
    – Hannah Vernon
    May 3, 2017 at 12:44

2 Answers 2

Reset to default
10

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

Improve this answer
2
  • 1
    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, 2015 at 22:03
  • For me yyyy-MM-dd was not available to select in the drop down (no idea why) so I had to go to Additional settings… in the above mentioned dialog, then go to tab Date and edit the text field for Short date: and enter yyyy-MM-dd.
    – bugybunny
    Dec 16, 2020 at 9:07
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".

Improve this answer
1
  • 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, 2015 at 11:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .