Windows Forum·65,707 discussions

Date problem with Excel

Locked

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.

Topic

Reply To: Date problem with Excel

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

Reply To: Date problem with Excel

In reply to Reply To: Date problem with Excel

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..

Reply To: Date problem with Excel

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.

Reply To: Date problem with Excel

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.”

Reply To: Date problem with Excel

In reply to Date problem with Excel

This question was closed by the author

[Author]

Replies