Converting Numbers To Dates

[Dave]

I have date data imported from Access which is appearing as a normal
date but it really is not. After formatting the cells as dates, the
cell format preview shows what appears to be a date (e.g. 07/15/2005)
regardless of whether I choose Date, Number or Text. A true date would
appear as a serial date if the number format was chosen, therefore I
believe it is text. If I manually re-enter the date in a cell formatted
as date, the problem is fixed. However, there are about 1000 rows.
Anyone know how to quickly convert them to valid dates so I can use
conditional formatting to flag dates before TODAY()?
Thanks.

 

[Peo Sjoblom]

Copy an empty cell, select all the text dates, do edit>paste special and
select add or select the whole column, do data>text to columns and press
finish, in both cases reformat the cells in question as dates

 

[Pete]

Another way is to add the formula =VALUE(cell), where cell is the top
cell with the "date" in. Format the cell as you wish, then copy down.
You can fix the values (Copy | Edit | Paste Special | Values | OK |
<enter>), then delete the first column of dates.

Regards,

pete

 

[Dave]

Peo, your suggestion doesn't work, or I don't completely understand it.
However, you did lead me to a solution, and for that, I thank both of
you for your help. Here's what worked for me:
-format source data (as Date), copy source data, select empty cell
-Edit>Paste Special & select "Values and number formats", select "ADD",
click 'OK' (at this point, checking format as Number revealed the
serial date, which was good)
-drag or cut & paste data back to origin.
Failure to choose the 'ADD' option will cause operation to fail.

 

[DennisSunga]

thank you also.

I just needed this same function now, searched the forum and foun
this.

It's most helpful. You've save me tons of time (and aggravation)

Denni