Date issues

[scottydogg84]

Hi all,

I need help converting several rows of text to dates.

For example, I have several cells in column A with text mmm dd yyyy (Feb 22 2011). I need to add this data to a report with several dates but obviously when I sort the data the text dates are sorted alphabetically and the dates are sorted in date order which is no good to me. I need the report all in date order.

Does anyone know of a simple formula to convert this text format (mmm dd yyyy) into a date format such 22/02/2011?

Thanking you in advance

 

[VoG]

Try

=DATEVALUE(MID(A1,5,2)&"-"&LEFT(A1,3)&"-"&RIGHT(A1,4))

 

[Ruddles]

What do you call 'simple'?

If the string is in A1, this formula should do it:-

=DATEVALUE(MID(A1,5,2)&"-"&LEFT(A1,3)&"-"&RIGHT(A1,4))

(The cell containing the formula needs to be formatted as a date.)

 

[barry houdini]

Welcome to MrExcel

You could convert a column of dates in that format using "Text to columns"

Select column

Data > Text to columns > Next > Next > under "column data format" choose "date" and then "MDY" from dropdown > Finish

or with a formula...

=REPLACE(MID(A2,5,7),FIND(" ",A2,5)-3,0,LEFT(A2,4))+0

where date is in A2

format in required date format

 

[scottydogg84]

Thanks all for the quick response however i'm still having problems with each of your suggestions:

VoG and Ruddles - both your suggested formulas returned "#VALUE!"? Maybe there is something else i need to do. I have formatted the formula column to date but no luck.

Barry Houdini - your suggestion worked well except on the dates where the day was one digit. For example 'Jun 29 2010' returned 29/09/10 which is great, but 'Jul 1 2010' returned "#VALUE!".

I'd appreciate any further help/advice.

You don't know how long i've spent on this!

 

[barry houdini]

All the suggested formulas work for me - I tested mine with 1 and 2 digit days......so I think there must be some different characters in your data than my test data,

If this data.....

Jul 1 2010

...is in A2 what do you get for a character count using

=LEN(A2)

Are you in the UK or US (might help to know your default regional settings)?

edit: perhaps try this one

=MID(SUBSTITUTE(TRIM(A1)," "," "&LEFT(A1,4),2),5,12)+0

 

[scottydogg84]

That's awesome! Worked a treat.

Just for your info, i had 12 characters for Jul 1 2010. Rather than being Jul 01 2010 there was an extra space before the one to compensate the zero 'Jul 1 2010).

Thanks again.