How do I convert text to a date?

[Martin Jones]

I have received a spreadsheet with a range of cells holding date
information, but in text format as follows:

'20070801' - i.e. the format is ''yyyymmdd'

Is there an easy way to convert these cells from text to a date
format, ideally 'yyyy/mm/dd'?

I could use something along the lines of (new cell) = LEFT(ref,
4)&"/"&MID(ref,5,2)&"/"&RIGHT(ref,2) and THEN change the format of the
resulting cell, but it seems very laborious - is there a smarter way?

thanks in advance

Martin

 

[Peo Sjoblom]

Best way if indeed that is the format, select the column, do data>text to
columns, click next twice until you get to step 3, under column data format
select Date and from dropdown select YMD (always select the format it was
received in so do not use your default date format), then click finish and
it will convert to Excel dates in the local regional setting

 

[Ron Coderre]

Here's a quick way....

Select the single-column range of "dates"

From the Excel main menu:
<data><text-to-columne>
Click [Next] until Step 3 of 3
Check: Date....Select:YMD.....Click [Finish]

(you may need to format those cells as dates)

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

 

[Martin Jones]

Ron/Peo - that's excellent... thank you very much indeed for your
SPEEDY reply!!

Martin