Formatting Text as Dates

[kathtee]

I have a spreadsheet, imported from Access, where the date column reads, for example, 20020101 for January 1,2002. I was instructed, in order to convert 20020101 to 01/01/2002 to use the DATEVALUE formula as follows,where A1 equals the cell address where the date I need to convert is residing: =DATEVALUE(mid(A1,5,2)&"/"&right(A1,2)&"/"&left(A1,4)). All I get as a result is #VALUE! though I don't get an error message stating something is wrong with the formula. The cell itself is currently formatted as "general". Help! I'm pulling my hair out! Thanks

 

[PaddyD]

Hi -welcome to the board!

Simpler solution - highlight you column of text 'dates'. Go to data | text to column. Click next twice. Select date as the 'Column data format' & pick the right option from the drop down. Click finish.

Paddy