change date format dates above = 13

donshome

New Member
Joined
May 11, 2015
Messages
8
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,485
Platform
  1. MacOS
Try

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

donshome

New Member
Joined
May 11, 2015
Messages
8
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

Well-known Member
Joined
Apr 29, 2003
Messages
6,485
Platform
  1. MacOS
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

New Member
Joined
May 11, 2015
Messages
8
sir it worked but year it is accepting as 1914 instead of 2014 any advice thanks for this much at least
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,485
Platform
  1. MacOS

ADVERTISEMENT

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

New Member
Joined
May 11, 2015
Messages
8
sir tried it but was unsuccessful have uploaded the file if u could remove just 5 minutes and change it i would be obliged
 

Forum statistics

Threads
1,136,445
Messages
5,675,900
Members
419,591
Latest member
mersanko

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top