Hi All,

Could someone help me with the following:

I have a range of dates which I want to convert from mm/dd/yyyy into

dd/mm/yyyy, my formula

(=MID(T1474,LEN(T1474)-6,2)&"/"&LEFT(T1474,2)&"/"&RIGHT(T1474,4))

works for most of the dates though in case of 1/12/2007 where the

month only has one position (instead of 01) my formula takes "1/" for

mm (instead of "01")

Can one of you advice how to capture this issue in my (or a different)

formula?

Many thanks!!!!

Rgds,

Robert

It seems from your formula that you want the result to be a text string, and

not a formatted Excel Date (an Excel date is a serial number formatted to look

like a date).

If that is the case, then the formula to use depends on the nature of the

original in T1474.

If T1474 is a serial number formatted to look like a date, then use this:

=TEXT(T1474, "dd/mm/yyyy")

If, on the other hand, T1474 is a text string, and you want to convert it to a

text string, then this should work:

=TEXT(DATE(RIGHT(T1474,2),LEFT(T1474,FIND("/",T1474)-1),

MID(T1474,FIND("/",T1474)+1,FIND(CHAR(1),SUBSTITUTE(

T1474,"/",CHAR(1),2))-FIND("/",T1474)-1)),"dd/mm/yy")

Obviously, there are various permutations of whether T1474 is text or a date,

versus what you want your result to be.

For example, if T1474 was a date, and you wanted your result to be a date, then

simply:

=T1474 and format the result (Format/Cell/Number/Custom Type: "dd/mm/yy")

--ron