Date problem

[Guest]

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!

 

[Guest]

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

 

[Roger Govier]

Hi Cheryl

Excel stores dates internally as the number of days since 01/01/1900 and
it is merely the formatting of the cell in the manner you choose that
will display either 05/09/2006 or 05 Sep 2006 (UK settings)

If you have formatted a cell Format>Cells>Date and then enter a number
like you have, Excel interpret that number a date that number of days
ahead of 01/01/1900 as you have found.
If you format the cell as General, then enter 090406, Excel will display
90406 as a number (leading zeros will be dropped).

You cannot enter dates by just typing in the manner you have tried
(unless you have an event code written in VBA to convert your entry),
you have to enter the date in one of the approved date formats.

So, don't worry that you have messed anything up, you haven't
If you want to speed up your data entry of dates, then having formatted
the cells as Date, you could just type 5/9 (or 9/5 in your case) and
Excel will assume that you mean the current year and will take the entry
as 05/09/2006 (09/05/2006).

Hope this helps.

 

[Guest]

Thanks much. Panic relieved
--
Cheryl

Hi Cheryl

Excel stores dates internally as the number of days since 01/01/1900 and
it is merely the formatting of the cell in the manner you choose that
will display either 05/09/2006 or 05 Sep 2006 (UK settings)

If you have formatted a cell Format>Cells>Date and then enter a number
like you have, Excel interpret that number a date that number of days
ahead of 01/01/1900 as you have found.
If you format the cell as General, then enter 090406, Excel will display
90406 as a number (leading zeros will be dropped).

You cannot enter dates by just typing in the manner you have tried
(unless you have an event code written in VBA to convert your entry),
you have to enter the date in one of the approved date formats.

So, don't worry that you have messed anything up, you haven't
If you want to speed up your data entry of dates, then having formatted
the cells as Date, you could just type 5/9 (or 9/5 in your case) and
Excel will assume that you mean the current year and will take the entry
as 05/09/2006 (09/05/2006).

Hope this helps.

 

[Guest]

You're right. In my panic, I forgot the date 'rules'. Thanks to you and to
Roger for prompt response.