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


  1. Peak Demand says:

    Great tip – thanks.

  2. Bram says:


  3. Prasanjit says:

    wow..never knew this function of text to column

    • Fine N. Sandra Nyarko-Badohu says:

      Very helpful. Something I was looking for hours, just solve in 3 seconds. Thanks

  4. Nilesh Shah says:

    thank you so much.. I was really struggling with American dates. Now this is helpful even segregating time from data comes from the system.

  5. Ioana says:

    This is awesome! Extremely helpful!!!! THANK YOU!

  6. CT says:

    Thank you so much. It works. This is the most helpful tip regarding this issue.

  7. If only Microsoft made simple single button click conversions for date, timezones, weights, measurements. This is a big time saver.

  8. Drakuun says:

    What if there is a time notation behind the bad date? Everything is messed up on some sheets…

    • Scott says:

      You would need to split the date and time into separate columns, either using a “text to columns” or doing a formula using something like LEFT(A1,8) or whatever to pull the date text out.
      Then you can work the date magic on the date, and recombine the new date and the original time columns if you need it in that format.

      • Ralse says:

        I tried that but unsuccessful. The person that sent me the sheet had already saved it once. This caused that bad dates to partially be recognised as ‘ok’ but switching month and day. The ones that were still bad I could do ‘left’ on but of course not in bulk anymore due to the mix. I then tried to filter them first and apply the conversion on the ones that were recognised before but it failed to do so correctly.

        • Scott says:

          Depending on the state of the data now, you may find doing a convert to text using something like TEXT(A1,”mm/dd/yy”) and then doing text manipulation on it.
          However, if some cells have already been correctly “formatted” you may have a problem with knowing if the current value (eg. 6/9/17) should be “9th June” or “6th September”
          Depending on how critical the dates are to this data set, my best suggestion would be to ask for a new copy of the original data.

        • Ralse says:

          I just told him to take the report again but before opening to change his region settings 🙂

        • Scott says:

          If that fixes it, however even then some reports may only present the data in a “static” format that may still be US based. Either way, as long as you know what that format is then you can manipulate it in a consistent manner to what you need it to be.
          Best of luck

  9. Patrick says:

    The above works brilliantly – however my US date/time formats needed cleaning up before the above worked, due to months having sometimes 1 digit and sometimes 2 digits!!

    Look at the 2 US Date/Time formats below, first has month of 1 digit (Sept), second has 2-digits (Oct)
    9/20/2012 4:00:00 AM +00:00
    10/12/2012 4:00:00 AM +00:00

    Here’s how I cleaned it up:
    1. If Column A has the US date, first extract to ColB the date only with no space characters using =LEFT(A2,FIND(” “,A2,1)-1)
    US Date/Time Cleaned
    9/20/2012 4:00:00 AM +00:00 9/20/2012 (no spaces)
    10/20/2012 4:00:00 AM +00:00 10/20/2012 (no spaces)

    2. I then had to copy/paste the second column to remove the formula before applying the TEXT to column

    US Cleaned Copy/Paste cleaned data (no formula)
    9/20/2012 4:00:00 AM +00:00 9/20/2012 -> 9/20/2012
    10/20/2012 4:00:00 AM +00:00 10/20/2012 -> 10/20/2012

    3. Now perform the above TEXT to Column suggestion on the Copy/Paste column (no formula present) to successfully obtain the UK date

    A B C D
    US Dates/Times Clean first No Formula UK Date
    =LEFT(A2,FIND(” “,A2,1)-1) Pasted col B here Text->Col
    9/20/2012 4:00:00 AM +00:00 9/20/2012 9/20/2012 20/09/2012
    10/20/2012 4:00:00 AM +00:00 10/20/2012 10/20/2012 20/10/2012

    • Scott says:

      Sometimes it can help to do a “dummy run” and set the text to column settings to bascially do nothing, then try the process again. The second time Excel will automatically use the previously used settings rather than trying to be smart and format the bits it can recognise and ignoring the bits it doesn’t, which leaves a bigger mess.

  10. John says:

    awesome tip! Thanks!

  11. Nishant says:


  12. Laura says:


  13. Pablo says:

    You rock! You just save me so much time to deal with this crap

  14. Dave says:


  15. zimocca says:

    Many thanks! I’ve bookmarked this page for future reference. Cheers.

  16. Jon says:

    Brilliant, thanks. This has also annoyed me for years. Pity you can’t apply that process to the entire USA and bring the awkward buggers into line.

  17. testofterror says:

    Thank you so much you save my soul

  18. Sab cheng says:

    Thanks so much for your tip!!!!

  19. Jon C says:

    Soooo much help . Many thanks

  20. Jef Fowler says:

    Very cool. I was not looking forward to converting (or at least trying to) UK date to US date. Great tip!

  21. Jacky Parsons says:

    Thank you so much!

  22. Kate says:

    Thank you so much. Given that the USA are the ONLY country in the world to use this format, why is not dd/mm/yyyy to standard?

  23. Jane says:

    Thank you so much !!

  24. Richard says:

    Thank you!!!

  25. Marakkar Ahmed says:

    This is really brilliant. Earlier I used to swap the position after after splitting date components. Thanks for revealing the hidden feature!

  26. Dinesh Sehra says:

    Wonderful. Worked flawless. Thanks a ton. I have been searching many forums. Most of them suggesting date to text and back, which anyways didn’t work as expected.

  27. Jeremy Parker says:

    Just tried this and it didn’t work for me. I was trying to convert May 2017 from the awkward American format (M/D/YYYY) to the British format dates.

    So for 1st May 2017 my date looked like this:


    When I used text to columns and the DMY format to convert I got this:


    So 1st May 2017 in American format has been converted to 5th January 2017 in British format. That was how the conversions went up until 12th May. 13th May to the end of the moth were simply ignored by this method and stayed in their American format.

    Is it the version of Excel I’m using?

    • Scott says:

      When you open documents, Excel will automatically try to format using the “default” values of what you last used. e.g. formatting or “convert text to columns”… Try setting things up with a blank sheet first to match your original document. You can also try importing your data from a txt/csv file, and during the import you can set the formatting for the columns, so be sure to set it to the matching US style. If you leave it as auto the Excel will try to work out what best fits and probably get it wrong.

  28. Ashley says:

    Brilliant and worked for me

  29. test says:

    How to change date format for a user based on the country he belongs to?

    For example,
    If a user is from US then the date format should be US date format I.e. MM/DD/YYYY.
    If a user is from India then the date format should be Indian date format i.e. DD/MM/YYYY.

    • Scott says:

      You need to know what format the original data is in, then do the conversion to match that. Once Excel has correctly identified it as “date” you can use the normal Excel cell formatting to display it in whatever regional format you prefer.

  30. Janet says:

    thank you

  31. John says:

    Thanks. Saved me a lot of stress.

  32. Anon says:

    It’s been driving me mad for the past hour – can’t thank you enough for this!

  33. Rob says:

    Thanks alot! Great tip

  34. Alexander says:

    OMG! That was awesome!!! You saved me so much time and efforts!!!! Thanks!

  35. Mike says:

    I was going mad trying to get Excel to format columns of copy-pasted date text, it kept giving me mixed formats – interpreting some as US or Caribbean and others as UK. After reading this article, I now just format the cells as text, paste as text, then use your method above to convert to MDY or DMY or whatever I want it to be. This has saved me a lot of time and my sanity – thanks very much!

  36. Rhi says:

    This has just made my year. No jokes.

  37. Jolly says:

    Excellent saved me a ton of time.

  38. Gerry says:

    Even after 5 years since the original post, it’s still saving people! Thank you!

  39. Nandu says:

    5 years from post date, this solution is still relevant and helpful. My problem was to convert international date format imported into excel into something excel recognizes (with my setup it only recognizes American format). I after two hours of frustration, I googled and your tip solved my problem in a few minutes. Thanks!

  40. Ives says:

    Your awesomeness deserves a monument, and a public holiday on the first Monday in August in perpeptuity! Thank you!!!!

  41. Dion says:

    Great 🙂

  42. Chitra Chen says:

    What a wonderfully simple solution, thank you!

  43. Awesome, man! So easy and quick solution for an annoying problem. Thank you.

  44. Oliver says:

    I was trying to do the opposite. Convert text dates in UK format to the US format that my Excel (for work reasons) is configured to expect. However, whether I selected DMY or MDY made no difference in the output. 02-09-2020 00:00 became 2/9/2020. Pity as it looked to be a really great way of solving the problem.
    I just wish the world obeyed the ISO format which is (I believe) 2020-09-02
    At least China gets it basically right.

  45. Ed says:

    Thank you! Great tip.

  46. Megan Green says:

    Took me 2 mins to fix after wasting 20 mins trying things that didn’t really make sense, thanks

  47. Christopher King says:

    Fantastic! Thank you so much.

  48. Thanks so much. This has saved me a lot of time and trouble!

  49. Z Mirza says:

    Excellently explained.

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s