Date changes to 0-Jan-00 when I leave cell

  • Thread starter Thread starter hvannest
  • Start date Start date
H

hvannest

The cell format is set to Number, Date, 14-Mar-01. I enter the date, and
when I press Enter, the date converts to 0-Jan-00. Any thoughts? Thank you.
 
What date did you enter? What does the formula bar show? What value do you
see if you temporarily reformat the cell to General?

You would, of course, see the 0th of January 1900 if your cell contained a
formula such as =12/5/2009, as 12 divided 5 then by 2009 is a number less
than 1, and Excel dates count in whole days from 0th Jan 1900.
 
What date did you enter? What does the formula bar show? What value do you
see if you temporarily reformat the cell to General?

You would, of course, see the 0th of January 1900 if your cell contained a
formula such as =12/5/2009, as 12 divided 5 then by 2009 is a number less
than 1, and Excel dates count in whole days from 0th Jan 1900.
 
A corollary of the second part of my reply below is that if you have Tools/
Options/ Transition set to enable "transition formula entry", then entering
12/5/2009 in the cell would change to =12/5/2009, so I would recommend that
you check, and if necessary disable, that transition option.
 
A corollary of the second part of my reply below is that if you have Tools/
Options/ Transition set to enable "transition formula entry", then entering
12/5/2009 in the cell would change to =12/5/2009, so I would recommend that
you check, and if necessary disable, that transition option.
 
I entered the date as 5/12/09. As I'm entering it, the formula bar shows
5/12/09. Once I press Enter, when I go back into the cell, the formula bar
shows =5/12/09 which I understand is what you're explaining below. If I
format the cell to General then I get 0.046296296, which also makes sense,
given the "=" in the formula bar.

Why would it be changing it from the date to a formula?
 
I entered the date as 5/12/09. As I'm entering it, the formula bar shows
5/12/09. Once I press Enter, when I go back into the cell, the formula bar
shows =5/12/09 which I understand is what you're explaining below. If I
format the cell to General then I get 0.046296296, which also makes sense,
given the "=" in the formula bar.

Why would it be changing it from the date to a formula?
 
Success - thank you!

David Biddulph said:
A corollary of the second part of my reply below is that if you have Tools/
Options/ Transition set to enable "transition formula entry", then entering
12/5/2009 in the cell would change to =12/5/2009, so I would recommend that
you check, and if necessary disable, that transition option.
Click to expand...
 
Success - thank you!

David Biddulph said:
A corollary of the second part of my reply below is that if you have Tools/
Options/ Transition set to enable "transition formula entry", then entering
12/5/2009 in the cell would change to =12/5/2009, so I would recommend that
you check, and if necessary disable, that transition option.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

COUNTIF doesn't work 12
01/00/00 appearing before date entered 1
VLOOKUP 00-Jan-00 3
sum columns between certain dates 1
Date Time formatting problem 7
WHEN I ENTER DATE IN EXCEL ... 2
Excel How to calculate in excel number of open actions that expired (older then today) 0
Blank 3

Back
Top