Convert between US and European date formats in Excel

tree7

The easiest way I know of (please let me know if you know a better way) to convert between US (“mm/dd/yy”) and European (“dd/mm/yy”) dates without using VBA in Excel is via “Text to Columns”. Let’s look at an example:
Convert date1
My system’s regional settings are setup for US dates, therefore I need to convert the dates to US format in order to make the Weekday function return a proper result. Here are the steps:

  1. Highlight the range of dates to convert (A2:A6)
  2. Click on “Text to Columns” in the Data ribbon
  3. Go with the defaults in the first two steps of the wizard
  4. Select “Date” as Column data format and pick the appropriate Format (DMY) from the dropdown
  5. Modify the Destination to paste the results somewhere else if necessary (needs to be on the same sheet)
  6. Click on “Finish”

ConvertDates2
ConvertDates3

shareThoughts


photo credit: Bouleau d’hiver, Megève, Haute-Savoie, Rhône-Alpes, France. via photopin (license)

Advertisements

2 thoughts on “Convert between US and European date formats in Excel

  1. How would that work when you have a Date and Time value ? My values are 1/02/2017 11:05:03
    Which is Januari 2nd. I need to convert it to 2/01/2017 11:05:03 – It’s not working via Text to columns, My guess because of the time that’s also present

    Like

    • Yes, this doesn’t work with Date and Time in one cell. You could make it work by separating the date and time parts into separate columns though:
      Step 1
      Step 2

      Afterwards, you can add the Time to the Date to have it again in only one column.
      Formula
      Hope that helps,

      Dirk

      Like

I'd love to hear what you think

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