How do I convert text to a date?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Martin Jones - 10 Aug 2007 21:24 GMT

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

Reply to this Message

Peo Sjoblom - 10 Aug 2007 21:35 GMT

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

Signature

Regards,

Peo Sjoblom

>I have received a spreadsheet with a range of cells holding date
> information, but in text format as follows:
[quoted text clipped - 11 lines]
>
> Martin

Reply to this Message

Ron Coderre - 10 Aug 2007 21:40 GMT

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

> I have received a spreadsheet with a range of cells holding date
> information, but in text format as follows:
[quoted text clipped - 11 lines]
>
> Martin

Reply to this Message

Martin Jones - 10 Aug 2007 23:15 GMT

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

Martin

Reply to this Message