Excel formula - convert date format of text

Question

I have the following column which is formatted as text and contains a date:

`Wed Jul 12 23:59:05 PDT 2017`

How can I convert this into an actual date in the dd/mm/yyyy format? I tried =DATE(RIGHT(B4,4),MID(B4,5,3),MID(B4,9,2)) but without success.

Thanks

Answer 1

Try this

=DATE(RIGHT(B4,4),MONTH(DATEVALUE(MID(B4,5,3)&1)),MID(B4,9,2))

See image for reference