Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date problem
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date problem
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date problem
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 FormatCellsDate 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. -- Regards Roger Govier "Cheryl" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date problem
Thanks much. Panic relieved:-)
-- Cheryl "Roger Govier" wrote: 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 FormatCellsDate 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. -- Regards Roger Govier "Cheryl" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date problem
You're right. In my panic, I forgot the date 'rules'. Thanks to you and to
Roger for prompt response. -- Cheryl "Toppers" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Static Date and Time code problem | Excel Discussion (Misc queries) | |||
problem with date sorting | Excel Worksheet Functions | |||
date scale problem | Excel Discussion (Misc queries) | |||
Date is being changed to a number (problem) | Excel Discussion (Misc queries) | |||
Imported Data Date Format Problem | Excel Discussion (Misc queries) |