Convert US Dates To UK Dates Using Excel - Purple Computing
date

How To: Convert US Format Dates To UK Format Dates Using Excel (Or Vice Versa)


Symptoms

You are working with a delimited list of dates and they are in the wrong country format, so your Numbers or Excel spreadsheet doesn’t recognise them.

For example, US dates are MM/DD/YYYY whereas UK dates are DD/MM/YYYY

Cause

If your Mac is set to UK localisation preferences, a big list of dates from an external source might not be recognised as dates in Numbers or Excel if they are in US format.

Worse still, it might read some of the dates incorrectly. For example 02/05/2016 in US format is February 5th 2016, but your spreadsheet (in UK mode) will detect it incorrectly as the 2nd May 2016.

When it tries to process a US date in UK format which is impossible e.g 02/25/2016, it will treat that as plain text rather than a date. In US format this is a valid representation of the 25th Feb, but in UK format you can’t have the 2nd day of the 25th month, so it is converted to a text cell. Nightmare.

Resolution

You need Excel for this, tested on 2011+.

Let’s say you want to convert US dates MM/DD/YY to UK DD/MM/YY:

  1. Paste the dates into a column in a new Excel spreadsheet
  2. Select the whole column
  3. Select the Data menu > Text to Columns
  4. Choose “Delimited” and click next
  5. Untick all Delimiters and click next
  6. Select the dropdown menu format “Date: MDY” and click Finish
  7. The dates will be swapped around to the UK standard and hence will be recognised as such if you then wish to order that column by date

Real-Time Feedback

When we solve a support ticket, clients are given the choice of leaving good or bad feedback along with an optional comment. We post the 10 most recent comments here automatically and in real-time. You can view even more on our page.

Date Name Comments
Nov 24th Andy K Exceptional service as always from the whole team, thank you Megan, Dean, Michael and Lochie : ) Many thanks, Chris and Andy
Nov 23rd Will Lochie, as always, was friendly, professional and fixed the problem (which was a dodgy virgin connection)
Nov 21st Walter O Purple Computing is our go too computer consultant. Competent, responsive, and focused on fixing our challenges permanently. I could not recommend Dean and his team more highly. They function as our IT department at a fraction of the cost.
Nov 20th Yvonne C Thanks Dean I didn’t expect such a prompt reply on a Sunday.! Much appreciated
Nov 18th Nigel T - well done James, great speedy service...
Nov 17th Sabira S very thorough and just one thing missed, which was fixed within 2 minutes of mentioning by email, top score...
Nov 15th Mark K Quick response that answered my question!
Nov 11th Leah E My issue was dealt with quickly and efficiently. I can't fault the service.
Nov 10th Ceri C Excellent service as always
Nov 7th Karen W Thank you Lochie for responding so quickly today Kindest regards Karen Book Keeper Space Kitchens & Bathrooms Ltd