Convert between US and European date formats in Excel


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”



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