Convert text to UK date

[uncleslinky]

Hey guys

I have a list of dates which are TEXT as follows: Fri 01 Aug 2014
Each 'date' is the same format DDD DD MMM YYYY

Is there a way to convert this text into an actual date which can be filtered and recognised by Excel. Ideally I would like the date to be shown in UK format as follows "DD/MM/YYYY"



Thanks
Rich

 

[azumi]

Maybe:

=DATE(--RIGHT(A1,4),MONTH(MID(A1,8,3)&1),--MID(A1,5,2))

and formatted as date

 

[shg]

Another way:

Row\Col	A​	B​	C​
1​	Fri 01 Aug 2014	01/08/2014​	B1: =--MID(A1, 5, 11)

 

[FDibbins]

Or this...
=MID(A1,5,99)

edit: is there an echo in here, shg...shg...hg......

 

[Teeroy]

To make sure the text extraction becomes a date you can wrap it in the Datevalue function:

e.g. =DATEVALUE(RIGHT(A1,LEN(A1)-4))

Ford, the text function used was changed for variety (and echo avoidance ).

 

[uncleslinky]

Thanks chaps! All working great :D