Excel date not populating correctly

March 9, 2012 at 15:25:36
Specs: Windows 7
Date list formatted X/XX/XXXX, but showing as 1/0/1900

See More: Excel date not populating correctly

Report •


#1
March 10, 2012 at 07:05:57
Have no idea what your problem is, you will need to expand on your explaination a bit.

MIKE

http://www.skeptic.com/


Report •

#2
March 12, 2012 at 09:38:55
I have formatted the cells as date X/X/XXXX, but when I enter in the date 06/11/2012 and click out of the cell, it comes up as 1/0/1900. If I click in the cell the formula at the top of the page says =6/11/2012, but as soon as I click out again it goes back to 1/0/1900.


Report •

#3
March 12, 2012 at 10:49:11
When you use the = sign, Excel performs the mathematical operation that you requested via your formula:

= 6 divided by 11 divided by 2012

That equals 0.000271101

Put 0.000271101 in a cell, format it as a Date and Excel will use the serial number of 0.000271101 to determine what date should be displayed.

See this site for an explanation of how Excel stores dates and times and you'll see why you are getting 1/0/1900.

http://www.cpearson.com/excel/datet...

Drop the = sign and you should be OK.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 12, 2012 at 10:53:02
Thanks for the response. However, the equal sign pops up automatically. Even if i delete it, as soon as I click out of the cell it pops up again.

Report •

#5
March 12, 2012 at 10:55:14
BTW, X/X/XXXX is not a valid Date Format in Excel.

m/d/yyyy and mm/dd/yyyy are, but X/X/XXXX is not.

When you tell us that you "have formatted the cells as date X/X/XXXX" we really have no idea what you are talking about - unless we guess. Once we start guessing as to what you really meant, we are apt to offer the incorrect solution.

Please try to be as accurate and complete as possible when posting questions in a help forum such as this since that makes it a lot easier for people to help you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
March 12, 2012 at 11:08:52
My guess is that you have the Transition option turned on.

Depending on what version of Excel you are using, the Options dialog box is either under Tools...Options or under the Office button at the top left of the screen.

Turning off the Transition option should prevent the = sign from being automatically inserted into the formula bar.

The Transition Option is typically used so that Lotus 1-2-3 files can be opened in Excel. I can't say why you have it turned on (if that is indeed the problem) or how it will affect your spreadsheet if you turn it off, but I haven't seen it used in many, many, many years.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
March 12, 2012 at 11:09:49
Sorry, yes it is m/d/yyyy.

Report •

#8
March 12, 2012 at 11:12:58
Thanks DerbyDad! That was it. It fixed it. I have been struggling with this for a long time.

Report •

#9
Report •


Ask Question