Changing date from dd/mm/yy format to mm/dd/yy format?

Copper Contributor
27/1/1922

to 1/27/1922

3 Replies

Select your dates, Ctrl+1 and apply custom d/mm/yyyy format

image.png

Thank you, this helped a lot ... but it still required a lot of manual editing.  When I first applied the appropriate Custom format (d/m/yyyy) to my first list it was able to correctly format about half of the dates on the list.  Then I tried using the opposite date format (m/d/yyyy) on the remaining dates and many responded awkwardly - for example, it would accurately change the display bar, but the excel file remained unchanged - weird ... and a few dates responded only to manual editing. I struggled through completing an accurate first list ... but too much work.

 

Since I had imported these date lists from who knows where, and since I had also edited fonts, etc., I suspected that the list was harboring unseen formatting gremlins.  I then attempted to clean up as much formatting gunk from the second list - I chose to change the font to Courier, save and file.  I reopened the Courier file and tried the Custom settings again, and this time it still was not completely formatted, but the ratio had improved to approximately 80/20. The remaining 20% then all responded easily to the other Custom format of m/d/yyyy, where mysteriously they would reconfigure to my intended d/m/yyyy.

 

Thank you, I kinda enjoyed tinkering with these dates ... and, I learned a lot.

 

 

Font format doesn't matter. In Excel dates are actually integer numbers, formatting only gives them human friendly interface. In your case part of values are date (aka integer numbers) and part are texts which only looks like dates.

 

In you sample all such texts are in dd/mm/yyyy form. You may select your column with dates/texts, on ribbon Data->Text To Columns and on third step of the wizard select type Date with YMD. You may replace current column or have an option to put transformed column into another one.

 

Do that one by one for both columns. After that select them and apply mm/dd/yyyy format. And after that you may apply font formatting, colors, whatever.

 

Please see second sheet in attached.