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.




Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

One thought on “Fix date format

  1. You can fix those values directly in place by using the Text To Columns dialog box. Select the column, bring up the Text To Columns dialog box… select Delimited on the first dialog page and click the Next button… make sure the checkbox labeled Other is not checked on the second dialog page and click the Next button… on the last dialog page, select the option button labeled Date and then select DMY (for day, month, year order which is the order the incorrect dates are in) from its drop down and click the Finish button. Your values are not real Excel dates.