Cheryl,
Entering 090406 is not a valid date format so when you enter
it and then format as date it treats 90406 as the internal Excel
representation i.e. day number 90406 with day 1 being 01/01/1900: hence you
get 07/09/2147.

Format a cell as Date, enter 07/09/2147, then reformat as text: you will get
90406 in the cell.

Dates should be entered as dd/mm/yy(yy) or dd-mm-yy(yy) (or US equivalent)

To convert 90406 to 09/04/06 use:

=IF(LEN(A1)=5,DATE(MID(A1,4,2),MID(A1,2,2),MID(A1, 1,1)),DATE(MID(A1,5,2),MID(A1,3,2),MID(A1,1,2)))

HTH

"Cheryl" wrote:

I seem to have messed up my date formatting in Excel. Have to confess that I
fiddled with the date checkboxes under the Calculations tab in Options. Now
when I type a date such as 090406 in any cell, any workbook and try to format
it as a date, it seems to be adding 90406 to 1/1/1900, and formatting the
date accordingly. In other words, the above is being interpreted as
7/9/2147. However, if I type it in as 9/4/06, it formats it correctly.

I am afraid to open any files where I have used dates in calculations
because they may become corrupted.

Can anyone out there help?

P.S. Let this be a warning against messing around when you don't know what
your doing!
--
Cheryl