A few years ago I wrote an article on extracting the end of quarter date from a date. I recently had a query that was related and I tweaked the previous solution to solve it.
Monthly Archives: May 2020
Fix dd.mm.yy date format
On a recent Webinar I was asked a question about an unusual date structure that was imported. The structure dd.mm.yy 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
=SUBSTITUTE(A2,".","/")*1
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.
Related Posts
Indian Financial Year Month Number in Excel
The Indian Financial Year start on 1 April. Like Australia its Financial Year month numbers can be painful. Here is a formula to sort them out.
Retrofit a Factor to an Excel Budget
Range name technique
It is common to have a Factor in a cell or cells in a budget to allow you to easily tweak the numbers by a percentage. If you want to add a Factor to an existing budget model here is how you can do it.