score:1

Accepted answer

So, using @Bathsheba suggestion, I went around this the long way but it works.

Try:

=DATEVALUE(CONCATENATE(MID(A1,1+FIND(" ",A1,FIND(" ",A1)+1),2),MID(RIGHT(A1,LEN(A1)-FIND(" ",A1)),1,3),RIGHT(A1,4)))

Explaination:

  • MID(A1,1+FIND(" ",A1,FIND(" ",A1)+1),2)-This finds the day in dd format from the string in A1

  • MID(RIGHT(A1,LEN(A1)-FIND(" ",A1)),1,3)-Takes the first three letters of the month from the string in A1

  • RIGHT(A1,4)-Takes the year from the string in A1

It then takes these values and concatenates them into the format of dd mmm yyyy. The DATEVALUE then changes this into the correct format of dd/mm/yyyy. This works for all dates in you format.

Note: The cell, which this formula lies, should be formatted for short date.

score:1

Custom format the cells as dd/mm/yyyy and use this formula:

=DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1)))

or without any additional formatting:

=TEXT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),"dd/mm/yyyy")

score:1

then your dates might need a bit of cleaning :]

=Text( Mid( Trim(Clean(A1)), Find(" ", Trim(Clean(A1)) ), 99) * 1, "dd/mm/yyyy")

Update

I just noticed the vba tag

[a1].NumberFormat = "dd/mm/yyyy"
[a1] = Split([a1], , 2)(1)        ' "Tuesday August 23, 2016" to "August 23, 2016"

Related Query

More Query from same tag