Convert american format text to date Uk format

[patrickmcclean]

Hi
I wonder if some one can help

I am extracting dates from another system to excel. The dates export to excel in a text format.

I want to convert the text format to a date format that can be sorted.
When exported it does not have the little green triangle in the top left corner of the cell.

So I can use the following approach

Cell A1: contains the date in text format
Cell A2: I can add =DATEVALUE(A1)
Cell A3: =A2 (with desired date format)

The problem I have is that the dates that are extracted to excel are in an American format and text. 11/29/2014
Any thoughts on how to convert to the uk date format and convert from text to date format such that they could be sorted e.g. 29 Nov 2014

 

[slinky]

You can do it this way in a formula.

RIGHT([American Date Value],2)&"/"&MID([American Date Value],6,2)&"/"&LEFT([American Date Value],4)