Solved: Worksheet formula to convert text date YYYYMMDD

[Dr.K]

A colleague gave me a workbook containing text dates in the YYYYMMDD format... since I don't have access to the original database, I can't fix the SQL. And changing the cell format (or using a custom format), does no good, becuase Excel still doesn't recognize dates. So, I think the best solution is a worksheet formula.

This is what I came up with.

=IF(C2<>"",DATEVALUE(MID(C2,5,2)&"/"&RIGHT(C2,2)&"/"&LEFT(C2,4)),"")]


Can anyone think of a better and/or more direct way to do this?

[lenze]

Select your column and choose Data>Text to Column. On the 3rd panel, select Date and YMD. Click Finish
You're done

lenze

[Dr.K]

DOH! Thats way easier, thanks!!

I will spread the word.