Date Conversion in Excel 2007

  • Thread starter Thread starter email4matt
  • Start date Start date
E

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?
 
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
 
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
 
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
 
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.
Click to expand...

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