Date Conversion in Excel 2007

[email4matt]

We've come across a strange problem in Excel 2007. We format a group
(doesn't matter the number) of cells to have a Date format of xx/yy/zz
(we've also tried other formats with the same result). After the
cells are formatted, we type in 31401. This should convert the date
to 03/14/01. This is what happened in older versions. Now, however,
it's converting those dates improperly. What I've determined is that
Excel is starting every date out as 01/01/1900. It then converts
whatever we enter into a number, and adds that many days to
01/01/1900.

For example, if I enter 012005, rather than converting that to
01/20/05, it adds 12,005 days to 01/01/1900 -- and displays 11/12/32
(or 11/12/1932).

Has anyone seen this?

 

[Bernard Liengme]

That is exactly how Excel works (all versions)
Dates are stored as serial numbers starting from 1/1/1900 (there is a 1904
option)
So if you type 3 in a cell and give it any date format you will see 3 Jan
1900 (in some format)

You cannot type 012005 and have Excel know you mean a date; how can it tell
you do not mean a number?

You could type '012005 in A1 and have
=DATE(RIGHT(A1,2)+100,LEFT(A1,2),MID(A1,3,2)
in B1 to convert to date. The leading single quote is there to preserve the
leading zero.
best wishes

 

[Harald Staff]

Sure, it's the way it is. Enter a big integer number in a datre formatted
cell and it does not convert to anything visually similar, but X days after
"day one" which is new year 1900. I'm impressed that you figured it out this
well, most people don't and report it as an error. See
http://www.cpearson.com/excel/datetime.htm
(but no, older versions didn't, without serious reprogramming, as seen on
http://www.cpearson.com/excel/DateTimeEntry.htm)

Best wishes Harald

 

[Gord Dibben]

31401 would return 12/20/85 if formatted to mm/dd/yy

Earlier versions of Excel would give the same results so you are
mis-remembering.

See other replies for details on how Excel stores dates as numbers.


Gord Dibben MS Excel MVP

 

[Bruce Sinclair]

That is exactly how Excel works (all versions)
Dates are stored as serial numbers starting from 1/1/1900 (there is a 1904
option)
So if you type 3 in a cell and give it any date format you will see 3 Jan
1900 (in some format)

You cannot type 012005 and have Excel know you mean a date; how can it tell
you do not mean a number?

You could type '012005 in A1 and have
=DATE(RIGHT(A1,2)+100,LEFT(A1,2),MID(A1,3,2)
in B1 to convert to date. The leading single quote is there to preserve the
leading zero.

Or you could separate (say 20/1/5) and format as you want it.