date

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

Tech How-To Microsoft Office How To: Convert US Format Dates To UK Format Dates Using Excel (Or Vice Versa)
By James Hart | Posted in Microsoft Office

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 reviews page.

Date Name Comments
Feb 19th Jack T Thank you!
Feb 18th Account... Lochie always sorts my queries out, thanks
Feb 14th Craig B Always a quick reponse time, always willing to help. Thanks Craig
Feb 6th Emily C Quick and very helpful service as always, thank you Lochie
Jan 23rd maddock... What a relief ! Extremely professional, friendly and above all knowledgeable...thank you so much James for helping to unravel our Mac mystery! We hope we won't need you again, but you are know our go to! Joyce & David Maddocks
Jan 22nd Lynn H Very efficient service. problem identified and rectified quickly. PC all up and running again. Many thanks Lynn
Jan 16th Jacqui F Fast and efficient.
Jan 8th Karen W Thank you so much Happy New Year Kind regards Karen
Dec 31st Laura D Exceptional support yet again had me back up and running in 15 minutes from me discovering problem and you were meant to be closed for Christmas. Thank you Lochie
Dec 19th Emily C Helpful and quick service as always, thank you