Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 11 down vote accepted

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

Screenshot of format cells
Click for full size

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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