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

Advertisements
Comments
  1. Peak Demand says:

    Great tip – thanks.

  2. Prasanjit says:

    wow..never knew this function of text to column

  3. 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.

  4. Ioana says:

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

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