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

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:

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?

Solution 1:

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

^{Click for full size}

Solution 2:

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.