change date format dates above = 13

[donshome]

i have a sheet of excel with date in the format dd/mm/yy i want to convert to mm/dd/yy
i did that with the formula text(a1,"mm/dd/yyyy"). and dates below 12 got converted however dates above 13 and 13 are not changing as per desired format can somebody please guide me to get the formula for dates =13 and above 13

 

[gaz_chops]

=+date(right(h19,4),mid(h19,4,2),left(h19,2))
=+TEXT(DATE(RIGHT(H19,4),MID(H19,4,2),LEFT(H19,2)),"mm/dd/yyyy")

That doesn't work!

 

[chetanmehra1989]

=IFERROR(TEXT(DATE(YEAR(H19),MONTH(H19),DAY(H19)),"mm/dd/yyyy"),TEXT(DATE(RIGHT(H19,4),LEFT(H19,2),MID(H19,4,2)),"mm/dd/yyyy"))

i think now its ok for you ...

 

[donshome]

sir how did you change column a i want to know that only what is the formula its exactly what iam looking for but unable to understand how u changed the column a thx

 

[donshome]

hi chetan i tried it btw thanks for your help but when i insert that formula it shows #num! so cant change it

 

[gaz_chops]

Did you try the file I uploaded?

 

[donshome]

Yes sir it is exactly what iam looking for but i cannot understand how you changed it can you give me the formula as in the second part you have put text(a1,mm/dd/yy) but the dates how you have changed in the a column i cannot figure out can you give in the formula thanks for helping me but a little more would be kind enough

 

[gaz_chops]

I gave you instructions in post #7 above!