Fix date format

On a recent Webinar I was asked a question about an unusual date structure that was imported. The structure was not recognised by Excel as a date. Here is formula that fixes it.

Below is an example of the date issue.

The formula in cell B2 is


As you can see the dates in column A are left aligned. That is a clue that they are not recognised as dates in Excel. Dates are right aligned.

The SUBSTITUTE function replaces the full stop between the numerals with a / and makes it look like a date.

This isn’t sufficient as the SUBSTITUTE function will return text. The *1 at the end converts the text date in to a real date that Excel recognises.

Note: Power Query can also automatically fix dates like these when it imports data.

Added 17/11/2021

As per a comment from Rick Rothstein Excel MVP you can use the Text To Column feature to fix the dates in place.