HElp:-American? Date conversion

  • Thread starter Thread starter Gerry
  • Start date Start date
G

Gerry

I have a sheet with american date format ie. MM/DD/yy.
but in some cases is on M/Dd/YY.
I need to convert to the rest of the world real date
format ie DD/MM/YY.
because of the m/ mm/ difference i am unable to use
mid...Any suggestions?
 
Hi,

You can add a helper column and enter this formula & copy it down.

=TEXT(A1,"DD/MM/YY")

Assuming A1 has the date in your format.

Regards

Govind.
 
Did try it but returned same value.
Receipt Date
4/21/2004 4/21/2004 (>=TEXT(f2,"DD/MM/YY"
10/7/2003 ?
 
Thanks Govind but No, it just reports back the same ie
4/21/2004 = 4/21/2004 (TEXT(F2,"mm/dd/YY")
but
10/7/03 =10/07/03 (TEXT(F3,"mm/dd/YY")
I am struggling
 
Thanks duncan, but because of the variable dates I have ie
4/21/2004
10/7/2003
1/1/2004
Mid may report back the seperator
 
Did try it but returned same value.
Receipt Date
4/21/2004 4/21/2004 (>=TEXT(f2,"DD/MM/YY"
10/7/2003 ?
Click to expand...

OK, that probably means that your data is TEXT and also that it has some
<spaces> or non-printing characters.

Did it come from a web page?

If so, try this:

For "F2" in your formula, substitute:

SUBSTITUTE(F2,CHAR(160),"")

or, if that doesn't work:

TRIM(SUBSTITUTE(F2,CHAR(160),""))

to clean it up.


--ron
 
Back
Top