With Power Query, you can get the difference between two dates in Excel. And in this tutorial, we will learn this in detail.
Count Days Between Two Dates
- First, open your data in the power query editor (Data Tab > Get Data > From Other Sources > From Table/Range.
- After that, go to Add Column > Custom Column.
- Now, in the Custom Column Dialog Box, name the new custom column and enter the formula (Duration.Days([Date 2]-[Date 1])).
- In the end, click OK to insert the new custom column.
In this formula, we deduct date 1 from the date 2, and then use the Duration.Days function to get the count of the days from it.
Even you can get the count of days by using the Duration.Days function. You can deduct date 1 from date 2, like the following example.
=[Date 2]-[Date 1]
And once you click OK, it will return a count in the new custom column. But you need to change the Column format to the whole number to remove the decimals format.
Date Difference from Today’s Date
And if you want to get the difference by using today’s date, you need to use a little bit different formula from the earlier one.
= DateTime.Date( DateTime.LocalNow() )-[Date 1]
This formula gets today’s date and deducts the date you specified from it. And once you click OK, it will insert a new column with the difference between dates. And you can change the formula of the days count Column to a whole number.
Get Count of Months Between Dates
And below is the formula that you can use to get the count of months between two days.
=((Date.Year([Date 2])-Date.Year([Date 1]))*12) + Date.Month([Date 2]) - Date.Month([Date 1])
This formula will return the total number of complete months between two dates in the result.
