33

I tried to enter this date in ISO 8601 format (YYYY-MM-DD) in Excel 2010: 2012-04-08, but Excel automatically converts the date format to 4/8/2012.

I tried scrolling through the different types of Date formats that Excel allows me to choose from, but the 'YYYY-MM-DD' format isn't there: enter image description here

Is there a way for Excel to recognize the ISO 8601 date format as a date value (and not automatically convert it to another format) when entered in a cell?

6 Answers 6

32

What you want is to use a custom format. Just type it in the box.

Screenshot of format cells
Click for full size

2
  • What about for Excel 2011? There is no box to enter a custom format!
    – Michael
    Jan 21, 2016 at 17:40
  • @Michael Sorry, I've not used nor do I have access to an OS X copy of Excel. Consider asking a new question addressing that version explicitly (and say why this question is not a duplicate).
    – Bob
    Jan 21, 2016 at 21:18
4

There's a MUCH easier, safer way to do this:

In dates, just change your locale to Australia and poof! ANSI standard dates are available AND if you're in the US, Canada, Australia, or New Zealand, it doesn't monkey with your currency format, as all those countries all use the $ symbol.

Why--in their infinite wisdom--Microsoft doesn't make this available to ALL locales is a mystery, but it has been this way for years.

1
  • Is this supposed to help with importing/recognizing dates or only displaying an existing date in the right format? I'm trying to figure out if there's a way to make Excel properly recognize standard ISO 8601 date and time representations like "2007-04-05T14:30" (example from en.wikipedia.org/wiki/…)
    – jacobq
    Oct 26, 2020 at 2:00
2

Excel recognized your entry of yyyy-mm-dd, which is why it automatically converted it to your Regional Short Date format (Windows 7: Start > Control Panel > Region and Language > Formats).

What you wanted was for Excel to recognize and display the format.

In addition to the above, if you want a date that responds to the user's short date format, add an asterisk *yyyy-mm-dd. I don't recommend it, but you can do it.

I once did a list where we wanted items sorted by date, but for posting on the Web, we wanted to display only the year and month. We had our Date column where we entered the date in full yyyy-mm-dd. In a second column (B), we converted the date (=A2) to show only the year and month with the cell format: yyyy-mm. When it came time to post, we sort by Date, copy the other needed columns, say B-D, and paste on the Web page. WOrked like a charm.

2

I had been using the custom formats before. I changed my system region (Control Panel > All Control Panel Items > Region) to English (United Kingdom), short date to "yyyy-MM-dd" and Excel then did not auto correct from "2015-10-06" to "10/6/2015". It did not auto correct the other way though, but if you enter it that way it will stay that way. Saved me some time for sure!

1
  • 4
    It's pretty crazy that you have to lie about your location to get an ISO standard date format!
    – Michael
    Jan 21, 2016 at 17:41
1

This is what worked for me instead: If it's a column of cells...

Select the column Data tab then text to columns (in xl2003 menus) choose fixed width, but don't have any delimiter lines Choose Date (ymd) This will convert the data to dates.

Now you can format the range the way you like. credit goes to Dave here: http://msgroups.net/microsoft.public.excel.worksheet.functions/how-do-i-convert-d/75235

1
1

Custom formats are the obvious solution.

If you don't want to let go of the mouse, you can scroll the locale up one option from "English (United States)" to "English (United Kingdom)" and the YYYY-MM-DD format will appear among the choices.

1
  • Op, the 'custom' list does include at least one ISO format: yyyy-mm-dd;@ (Excel 2013) Jun 9, 2015 at 22:44

You must log in to answer this question.

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