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)?

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

^{Click for full size}

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.

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.