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)
Tech thoughts 
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
LikeLike
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:
Afterwards, you can add the Time to the Date to have it again in only one column.
Hope that helps,
Dirk
LikeLike