Text tring to date

[jag108]

Hi

I need to convert this text string into a standard date fromat.

each cell is formatted like "4 Mar 2007 Su" I need it to be 20070304

I have managed to seperate out the different parts i.e.

04 = day_val
"Mar" = month_val
2007 = year_val

But I am having the most difficult time getting them back to the date format mentined.

I have about 1700 entries that will need to be converted.

 

[XLGibbs]

Assuming you need to trim the day piece off the end this formula can be copied down referring to the cell of the string (my example uses A1)

=DateValue(Left(A1,10))

Format the entire column as Custom Date

Format>Cells>Custom and enter this :

yyyymmdd

Edit:

The length of the date would change for 2 digit days so use this

=DateValue(Trim(left(A1,11))

Book1
	A	B	C	D
1	4 Mar 2007 Su	20070304
2
Sheet1

 

[jag108]

Thank you very much Gibbs