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]

Try

=DATE(RIGHT(A1,2),LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/",A1)+1,2))

 

[donshome]

sir tried it but am unsuccessful it is giving some other date
i cant see how i can attach the file and show it to u

 

[gaz_chops]

Might have got it the wrong way round!
Try

=DATE(RIGHT(A1,2),MID(A1,FIND("/",A1)+1,2),LEFT(A1,FIND("/",A1)-1))

 

[donshome]

i have uploaded the file in http://s000.tinyupload.com/?file_id=26813757145679353687
for your refrence please advice and help

 

[donshome]

sir it worked but year it is accepting as 1914 instead of 2014 any advice thanks for this much at least

 

[gaz_chops]

Ok I would try
Highlight column A
Select Data, Text to columns
Select next until you see option "Column Data format"
Select Date, DMY
Select finish

Then use your original formula to convert to "mm/dd/yy"

 

[donshome]

sir tried it but was unsuccessful have uploaded the file if u could remove just 5 minutes and change it i would be obliged

 

[gaz_chops]

Uploaded to Dropbox

https://www.dropbox.com/s/natvftk3xjc20qx/Book11.xlsx?dl=0

 

[chetanmehra1989]

uploaded to dropbox

https://www.dropbox.com/s/natvftk3xjc20qx/book11.xlsx?dl=0

=+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")