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:
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:
-
Highlight the range of dates to convert (A2:A6)
-
Click on “Text to Columns” in the Data ribbon
-
Go with the defaults in the first two steps of the wizard
-
Select “Date” as Column data format and pick the appropriate Format (DMY) from the dropdown
-
Modify the Destination to paste the results somewhere else if necessary (needs to be on the same sheet)
-
Click on “Finish”
photo credit: Bouleau d’hiver, Megève, Haute-Savoie, Rhône-Alpes, France. via photopin (license)