Correct date as number

[agibsonsw]

Hello. Excel 2003.

We have columns of dates in the format '20110314' and I can use the formula
=DATE(LEFT(D1,4),MID(D1,5,2),RIGHT(D1,2))
to re-convert them to date values.
But I was hoping it was possible to do this by applying the custom number format 'yyyymmdd' - no joy..

Is there another method to recommend? Andy.

[HansV]

Excel stores dates as numbers: the number of days since December 31, 1899. For example today (March 14, 2011) is stored as 40616.
So the number 20110314 does NOT correspond to the current date, even if you format it as yyyymmdd.

Another possible formula is

=DATE(D1/10000,MOD(D1,10000)/100,MOD(D1,100))

[agibsonsw]

Thanks, Andy.

[rory]

You could also use:
=TEXT(D1,"0000-00-00")+0

[agibsonsw]

That's clever. How does Excel know it's the year-month-day? I suppose it must be a default date format somewhere?

Andy.

[rory]

It's the ISO standard I believe.

[HansV]

yyyy-mm-dd is the ISO 8601 date format, and it's the official format in several countries.