Date Format Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I try to format cells as "Date" Type "03/4/97" but it displays as 7/28/0
1/25/04, (which I enter as 12504) shows as 3/26/34.. Even if I use Custom format, as m/dd/yy, it still displays wrong
 
Stu,

If you enter a 'date' without any date separators, Excel will treat it as a
number, and enter that. As a date is just a number since 1 Jan 1900, when
you format as a date, it will calculate the date from the number you
entered. 28th July 1909 is 3497 days since 1st Jan 1900,

So you need separators, or you could try using VBA as Chip Pearson shows at
http://www.cpearson.com/excel/DateTimeEntry.htm

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Stu said:
I try to format cells as "Date" Type "03/4/97" but it displays as 7/28/09
1/25/04, (which I enter as 12504) shows as 3/26/34.. Even if I use Custom
Click to expand...
format, as m/dd/yy, it still displays wrong, 26th March 1934 is 12504 days
since then, which are the numbers you are inputting.
 
The clue is in your second line. Although the cell is formatted as a date,
Excel stores dates a a number. I forget what date they are numbered from but
28 July 1909 is day number 3497 and 26 March 1934 is day 12504. You need to
add a separator such as "/" when typing the dates. If you leave off the
year, Excel will assume the current year (eg 1/25 will be formatted as
1/25/04 with your custom format)

Ian

Stu said:
I try to format cells as "Date" Type "03/4/97" but it displays as 7/28/09
1/25/04, (which I enter as 12504) shows as 3/26/34.. Even if I use Custom
Click to expand...
format, as m/dd/yy, it still displays wrong
 
You have to enter the date format with the slashes or use VBA
How would you expect excel to tell the difference between
twelve thousand five hundred four and January 25th 2004.
Format can never change the value, it only displays it differently and
excel will see your "date" as 12504 days after January 0 1900
thus March 26 1934.
For a workaround using a macro see

http://www.cpearson.com/excel/DateTimeEntry.htm



--

Regards,

Peo Sjoblom

Stu said:
I try to format cells as "Date" Type "03/4/97" but it displays as 7/28/09
1/25/04, (which I enter as 12504) shows as 3/26/34.. Even if I use Custom
Click to expand...
format, as m/dd/yy, it still displays wrong
 
Thanks guys, I thought that if formated as a date, it would automatically format it with the separators.
 
HI,

How can I have fill color of cell change according to due date. In
example, red overdue, green - on time, black complete.

Thanks, K
 
Back
Top