If you have a column of dates in Excel that are in US format, it is easy to convert them to UK format. Simply follow these steps:
- Select the column containing the US Dates:
- From the Data Ribbon, Choose “Text to Columns”:
- Choose “Delimited” from the Text to Columns Wizard, then click next.
- Untick all the delimiters.
- In the column data format area, choose “Date” and select “MYD” from the drop down menu:
- Excel will now recognise the column as dates and you use format cells to select the UK date format, or any format that suits you.
How is a video demonstration of how to convert US Dates to UK Format:
Nathan Ashe says
Why is this so hard Microsoft!
The above solution has not worked for me
yes, i can change the cell format to UK (rest of the world other than the Stupid USA) date format.
But when I’ve already got data that is in the US format EG: 6/12/2019 and want to change it to 12/06/2019 there seems no way.
Excel always fails when the date contains a combination that can work either way.
Sure, 06/27/2019 must be the 27th of June 2019 but 6/12/2019 could be the 6th of December or the 12th of June….
In this case it needs to be the 12th of June but I cannot get excel to convert this
leo says
it works for me and save tons of work
thanks so much!!!
himanshu mehta says
you are so AWESOME!!! thank you so much for the clear instructions and that actually fix my problem – I am so super happy right now!
Atul Srivastava says
I have different problem. I have mixed values in a column. Some cells have values like 25-02-2019 11:20 and some cells have values 02-25-2019 11:20. I am not getting any method to convert all of them to either UK format or US format. Any solution?
Paulie says
You’re probably going to have to use another column with an “if” formula. But using PowerQuery to import/transform the data would be by far the easiest solution.
Dean says
Faboulous worked like a dream!!!!!
CJ says
Thank you for taking the time to put this tip online – was easy to follow and worked perfectly
Alistair Campbell says
I think you have the source format wrong in step 5 – given the dates you are converting (ie MM/DD/YY to dd/mm/yy) you should select the MDY format, not MYD as you have shown?