|  | Date Conversion in Excel 2007 | | |
| | | Guest |  |
|  Posted: Tue Jun 10, 2008 4:31 pm Post subject: Date Conversion in Excel 2007 |  |
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 | |
| | Posted: Tue Jun 10, 2008 4:31 pm Post subject: Re: Date Conversion in Excel 2007 | |
| |  | |
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
--
Bernard Liengme
Microsoft Excel MVP
LINK
<email4matt@yahoo.com> wrote in message
news:3c72a608-ea86-41c5-a7fd-6a7d24ba769e@34g2000hsh.googlegroups.com...
| Quote: | 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? |
|
| |
| | | Harald Staff | |
| | Posted: Tue Jun 10, 2008 4:31 pm Post subject: Re: Date Conversion in Excel 2007 | |
| | | |
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
LINK
(but no, older versions didn't, without serious reprogramming, as seen on
LINK)
Best wishes Harald
<email4matt@yahoo.com> wrote in message
news:3c72a608-ea86-41c5-a7fd-6a7d24ba769e@34g2000hsh.googlegroups.com...
| Quote: | 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? |
|
| |
| | | Gord Dibben | |
| | Posted: Tue Jun 10, 2008 4:31 pm Post subject: Re: Date Conversion in Excel 2007 | |
| | | |
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
On Tue, 10 Jun 2008 09:31:27 -0700 (PDT), email4matt@yahoo.com wrote:
| Quote: | 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? |
|
| |
| | | Bruce Sinclair | |
| | Posted: Tue Jun 10, 2008 9:26 pm Post subject: Re: Date Conversion in Excel 2007 | |
In article <OtjdnoxyIHA.1236@TK2MSFTNGP02.phx.gbl>, "Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote:
| Quote: | 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.  |
| |
|
|
