Converting US dates to UK format in Excel

Posted: August 5, 2014 in Microsoft Office, Solved

This has bugged me for YEARS.

Take a column full of US dates in Excel that have been formatted as text and convert them to a “proper” format that can actually be used to sort by… i.e. the date format used by the rest of the world.

While annoying, this is relatively easy if all your dates are in fixed width mm/dd/yy (e.g. 03/12/14) or mm/dd/yyyy format, you can just use simple mid/left/right formulas to extract them knowing exactly where each will be.

If on the other hand the dates are in m/d/yy (e.g. 3/5/14 or 25/11/14) or m/d/yyyy format then it gets a bit messier as you now need to start finding and splitting the text at the slashes.

Today I found a really simple way to do it.

Without using overly complex formulas (or any at all), without needing to export and import or anything else.

Example to convert dates currently in US m/d/y format to UK d/m/y:

  1. Select the column of dates
  2. Under DATA, select “Text to Columns”
  3. Select Delimited – Next
  4. Untick ALL delimiters – Next
  5. Select column format “Date: MDY” – Finish
    1. Note this data format should be what the dates are currently in, not what you want them to be
  6. The text will now be recognised by Excel as a date and appear as right-justified in the column
  7. Right-click the still highlighted column (or select it again) and open Format Cells and change date format to the style you require

Simple

Comments
  1. Ify says:

    You are such a lifesaver! I had been banging my head on the wall for the past half hour looking for a way around this. Thanks so very much.

  2. Townsend says:

    Genius! thank you.

  3. Frederic Yu says:

    Thank Yu SO MUCH!!!! I spent more that half hour trying to figure it out. Other ways on Google are so complicated.

  4. Emma says:

    Brilliant! You just saved me so much time. Thank you!!!

  5. Brilliant, but this only seems to work if the dates are already formatted as US dates. If they aren’t (e.g. if they are just formatted as Text) you will first need to go to Format and format them as US dates. Then I found that in Step 5:
    Select column format “Date: DMY” – Finish

    I had to select
    Select column format “Date: MDY” – Finish

    Then it worked perfectly!

  6. CanCount says:

    Thank you. I had forgotten about this solution and you saved me lots of time trying to remember!

  7. mmw says:

    Brilliant tip!

  8. david gee says:

    Amazing saved my ass

    • Moys says:

      Since we are talking about changing annoying Americanisms, I think this tip actually saved your ‘arse’….. (Unless you have a computer literate donkey in the garden?)

  9. Divyesh Modi says:

    Really Brilliant tip

  10. Jim. says:

    Thanks for sharing this. Easy instructions for a not particularly obvious solution.

  11. ronniegatsby says:

    This is such a great tip. Thanks.

  12. Enamul says:

    Fabulous. Thanks maestro.

  13. Rashed says:

    Thanks a million!

  14. Prabhu says:

    Great .. now reports are much easier to handle …

  15. Lady Croft says:

    That’s pretty rockandroll, thank you! One small thing; under step 5, it’s helpful to define that you have to select the column format the dates are currently in, not the one you want them to go to. So from US to UK it’s m/d/y

  16. otterman says:

    This is lovely, thank you very much!

  17. lenn says:

    you saved me. thank you

  18. Aprylle says:

    You are literally the best person in the whole world!!! THANK YOU x 1,000,000,000.

  19. Miruna Dogaru says:

    THANK YOU!

  20. Aidan says:

    Brilliant! Thank you!

  21. kenneth says:

    this is not working. while in the cell is shows the correct format , but the runner on top the sheet is shows the different format itself.

    • Scott says:

      you may be seeing the original data in the top rather than the formatted data as it shows in the spreadsheet.
      Also, check the regional settings on your computer to make sure they are correct.
      This process doesn’t *change* the data, just the formatting of how it is displayed.

  22. vivek vaidya says:

    i am not able to convert 5/27/2016 7:02:22 AM -04:00 this type of format to dd:mm:yy hh:mm
    my location is india plz reply

    • Scott says:

      Is that a time in UTC with “-04:00” in the cell? My guess is it doesn’t recognise it as a date/time with that included.
      I’d suggest splitting/removing the time zone part and adding/subtracting it after the conversion is complete

  23. lee says:

    thank you

  24. Ruben says:

    Man, that was incredibly useful. Thank you a million!!

  25. Bernie says:

    Brilliant and so easy! Many thanks for sharing.

  26. Peter Gackenbach says:

    Thanks!!!!!! I knew there had to be a way but I had so many headaches from working with data from multiple countries and excel never reading the date as the format I wanted. Such a time saver!

  27. Louise says:

    Thank you ever so much. Quick and easy solution : )

  28. Colin Coles says:

    Sorry to spoil the party but if you save the date from a form onto a worksheet with format “dd/mm/yyyy” it won’t work. It still comes in as “mm/dd/yyyy” and won’t convert with the Text to Columns – especially if the date has a leading zero. Still looking for a solution…

    • Scott says:

      Can you explain a bit more what you are doing? There is no reason I am aware of why it shouldn’t work, so I would look at the format of the data you are importing. What is the “form” you are referring to?
      Perhaps you can try cleaning out any weird formatting by opening your data in Excel, then copy “Paste Special as text” to a new document, then save and do the import using that cleaned version of the data. Leading zeros are not a problem for any testing I’ve done.

  29. Maya says:

    love this! I’ve been struggling with this issue for ages…. I actually gave up at some point!

  30. Phil says:

    Fantastic! Just what I needed to solve a really irritating problem. Thanks.

  31. Simon says:

    Amazing, thank you – you just saved me a lot of time!

  32. CurlyPig says:

    Brilliant – thanks

  33. Natasha says:

    Yes, Yes, YES!! Thank you

  34. sharon says:

    thankyou so much for this!!!!

  35. Lynnette New says:

    OMG… thank you thank you thank you!!!! This has saved me hours of manual changes!!!

  36. Sarah says:

    I love you, you saved my life

  37. Sue F says:

    Never even knew that function existed – an epic workaround!

  38. CR7 says:

    thanks!!!!! saved me!

  39. Isobel Valentine says:

    I felt myself aging doing it before you left this incredibly useful tip!

  40. Mahen L says:

    saved a ton of time thanks friend🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s