Date conversion issue (Excel 97)

Topic

New Reply

I have file (45,534) records and it was sent as a txt file. The user sent it to me in an excel format. They can not sort correctly by date. The date field looks like a date but it really is not. I know you can press F2 to “reformat” the field and when I do this it sorts correctly – in date order. I have a lot of records, is there a way to do the F2 function for all records at the same time? Currently, I am clicking on each one and press F2 and then enter.

Thanks,

Reply | Quote

Replies

Try the following:
– Select an empty cell.
– Copy it to the clipboard (for example by pressing Ctrl+C).
– Select the column or columns with date values that are not dates.
– Select Edit | Paste Special…
– Click the Add option, then click OK.
– Format the selected column(s) as a date again.

Reply | Quote

It worked great. Explain to me why you used the ADD option.

Reply | Quote

Dates are stored in Excel as numbers (the number of days since December 31, 1899). In your file, they were text values. The Paste Special operation forces Excel to interpret them as number values by adding an empty cell, i.e. 0 to each value. The formatting then displays the values as dates again.

(You could also have selected a cell containing the number 1, copied it, then Paste Special with the Multiply option. It would have had the same result)

Reply | Quote