General discussion

Locked

Date problem with Excel

By RG11 ·
I have office xp pro with sp1, when in excel and entering a date into any date formatted cell, it returns a different date other than what I enter.
For instance if I type 010604 into a cell that should display Jan 1, 2004 it will show some odd date like Mar 4, 1928.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by DKlippert In reply to Date problem with Excel

You must enter a delimiter."-" or "/"
To enter without a separator see Chip Pearson's discussion:
http://snipurl.com/3olx

Collapse -

by RG11 In reply to

Depending on the date format of the cell it will enter those delimiters in. I could enter in 010704 and press tab and it will show 4/21/29. If I enter the delimiter in it will work fine but I am trying to avoid that..

Collapse -

by Wyrmlord In reply to Date problem with Excel

Of course it does! What you are entering is in fact a NUMBER, an ordinal counting up from 01-01-1900, if you will. So Excel adds up 10604 days to the above date and arrives at Jan 11, 1929.
This is common to all Excels I worked with (97 and up) and is a convention, not an error. Just use delimiters, then.

Collapse -

by DKlippert In reply to Date problem with Excel

"Depending on the date format of the cell it will enter those delimiters in."

It is just displaying the date that corresponds to the number enterd. i.e. 500 displays the 500th day since 1/1/1900 ? 5/14/1901.

"If I enter the delimiter in it will work fine but I am trying to avoid that.."

That's why I recommend Chip Pearson's solution.

http://snipurl.com/3olx

"This page describes the VBA procedures that will allow you to enter dates and times, without having to enter in the "/" or ":" separators. For example, you could enter 9298 and get the valid date 2-Sep-1998. Or you could enter 1234 and get the valid time 12:34:00 PM.

These methods use the Worksheet_Change event procedure to catch changes made to the worksheet."

Collapse -

by RG11 In reply to Date problem with Excel

This question was closed by the author

Back to Windows Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums