Converting text to date

[rosysnozzy]

Hi, I have imported data that is showing dates as 18th Sep 2013. Excel does not like this and I have tried various methods of conversion but nothing seems to work. I will be getting data that has Oct, Nov, Dec etc so the formula needs to be able to handle these too. I would like these 'dates' converted to excel friendly dates. Can any one help please?

Thanks,

Ros

[Pete_UK]

Can you submit a sample workbook (the FAQ describes how to), so we can see the variations you are likely to encounter.

Pete

[rosysnozzy]

Hi thanks for the quick reply! Wookbook attached.

[Pete_UK]

Well, if that's all you are going to get then you could do this in B1:

=DATEVALUE(SUBSTITUTE(A1,"th",""))

and copy down.

But, I suspect you will also have 1st, 2nd, 3rd, 21st, 22nd, 23rd in among those dates, so you need to remove the other ordinal initials. Is that the case?

Pete

[newdoverman]

This will work for part of the problem (A1 and A2 but not A3) or for other numeric endings such as st rd nd etc.

=SUBSTITUTE(SUBSTITUTE(A1,"th ","/")," ","/")*1

The DATEVALUE formula may serve your purposes better.

[Pete_UK]

If you want a more universal formula, put this in B1:

=IF(A1="","",DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"th "," "),"st "," "),"nd "," "),"rd "," ")))

then copy down as far as you like.

Hope this helps.

Pete

EDIT: this will only give you the date - it will remove any time element you might have in there (like your 3rd example).

Also, you should format the cells as dd/mm/yyyy.

Pete

[AlKey]

This will take care of "th, nd and rd"

=DATEVALUE(SUBSTITUTE(A1,MID(A1,SEARCH(" ",A1)-2,3)," "))

[newdoverman]

To have the time as well as the date insert a helper column and use the Text to columns feature on the Data tab to split the hours and minutes into column B. In column C enter this formula to reassemble the data as a date. (format as dd/mmm/yyyy h:mm)

=DATEVALUE(SUBSTITUTE(A1,MID(A1,FIND(" ",A1,1)-2,2)," "))+B1

This is pretty much a combination of previous suggestions with the addition to get the hours and minutes.