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.
Correct date as number
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Correct date as number
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Correct date as number
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))
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))
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Correct date as number
Thanks, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Correct date as number
That's clever. How does Excel know it's the year-month-day? I suppose it must be a default date format somewhere?
Andy.
Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Correct date as number
yyyy-mm-dd is the ISO 8601 date format, and it's the official format in several countries.
Best wishes,
Hans
Hans