convert number to date in excel 2003

[Wiji]

I used to be able to type 070509 for date and then it'd automaticly converted
to 07-05-2009. But now it shows 15-01-2093 instead of 07-05-2009! Why? And
how to change it that i would be able to convert or change the format to the
exact date and year I want?

 

[Dave Peterson]

Unless you were running some kind of macro or using a formula, then typing
070509 would never result in a date like 07-05-2009.

The reason that you're getting that date way out in the future is because of the
way excel treats dates. To excel, a date is just the number of days since a
starting date (usually Dec 31, 1899 in wintel land).

Chip Pearson has an event macro that can be used to quickly enter dates:
http://www.cpearson.com/Excel/DateTimeEntry.htm

Or you could use a formula in another cell that parses your entry and translates
it into a date.

But I'm not sure if 070509 is July 05, 2009 or what...

 

[Gord Dibben]

If you used to be able to do that you must have had some event code behind
the worksheet.

Excel has never natively been able to do that with any type of formatting.

You can use a helper columnwith formulas to make the necessary changes or
Ron de Bruin's QED add-in.

http://www.rondebruin.nl/qde.htm

What works most of the time is to enter the dates as your example then run
them through Data>Text to Columns.

Depending upon your Regional date settings you would get 05-07-2009 or
07-05-2009

When describing problems with dates it is best to provide a non-ambiguous
date where month and day are easily recognized.


Gord Dibben MS Excel MVP