Strange Formatting Problem

[CCHRdept]

I have a workbook containing serveral worksheets and there is only a formatting problem with one of the worksheets. I accidentally hit control/something when I was working on this particular worksheet. This action automatically changed the dates to large numbers (i.e. 8/28/02 now reads 37496). The trick is that in the menu bar describing what is contained in the cell next to the = sign appears the date 8/28/02 and not the number? When I enter a new row and new date, the date automatically changes to a large number -- going to the formatting menu does not change the cell format?

 

[Anne Troy]

It appears you've somehow applied text formatting and now Excel won't give back your date.

Use the Times One Fix:

Put a one (1) in any blank cell.
Copy the cell.
Select all your bad dates.
Hit Edit-Paste special, Multiply, Ok.
Now you should be able to format the cells as dates.

See:

http://www.cpearson.com/excel/datetime.htm#SerialDates

 

[Damon Ostrander]

Hi Kirsten,

Actually, this is not strange at all. In Excel the underlying value for all dates is simply the number of days elapsed since December 31, 1899. Apparently whatever you did told Excel to display the number as numbers rather than dates. To get them back to being represented as dates simply select all the cells to be formatted as dates, then go to Format > Cells > select Date in the Category list, and select the desired date format in the Type list.