Power Query: How to calculate the difference between two dates

In this article we will show you how to calculate the difference between dates and times in Power Query.

We include how to count the number of days, months and hours between two dates or from today, as well as the number of hours between two times.

If you want to know how to calculate the difference between two dates in Power BI (rather than Power Query) then we have a separate guide for this.

Calculate the number of days between two dates in Power Query

If you want to calculate the number of days between two dates in Power Query you can use the Duration.Days function.

First, create a new custom column in your table.

Then, use Duration.Days and input the last date minus the first date.

In our example, we want to find out the number of days an employee has been with our company. Therefore we will minus the Date Left from the Date Started.

Here is the custom column formula we would use:

=Duration.Days([Date Left] - [Date Started])

Here is the resulting custom column:

Calculate the number of days between today and a date in Power Query

If you want to calculate the number of days between today and another date in Power Query, you can use the Duration.Days function in addition to DateTime.Date(DateTime.LocalNow()).

Duration.Days is still used in this case to determine the difference between two dates, however one of the dates must be today.

Rather than use DateTime.LocalNow() we must wrap this with the DateTime.Date function to return a date for our calculation.

Here is the custom column formula we would use:

=Duration.Days(DateTime.Date(DateTime.LocalNow()) - [Date Started] )

Here is the resulting custom column:

Calculate the number of months between two dates in Power Query

If you want to calculate the number of months between two dates in Power Query, you can combine Date.Year and Date.Month to do so.

Here is our example custom column where we have calculated the difference in months between Date Started and Date Left:

=((Date.Year([Date Left])-Date.Year([Date Started]))*12) + Date.Month([Date Left]) - Date.Month([Date Started])

Here is the resulting custom column:

Calculate the number of hours between two dates in Power Query

If you want to calculate the number of hours between two dates in Power Query, you can use Duration.Days times by 24 to find the number of hours between two dates, and then add the number of hours between the two times using Duration.Hours.

Here is our example custom column that calculates the number of hours between the Date Started and Date Left:

=(Duration.Days([Date Left] - [Date Started]) *24 ) + (Duration.Hours([Date Left] - [Date Started]))

Here is the resulting custom column:

Calculate the number of hours between two times in Power Query

If you want to calculate the number of hours between two times in Power Query, you can use Duration.Hours to find the number of hours between two times and then calculate minutes by using Duration.Minutes divided by 60.

Here is our example custom column that calculates the number of hours between Date Started and Date Left:

=(Duration.Hours([Date Left] - [Date Started])) + (Duration.Minutes([Date Left] - [Date Started]) / 60)

Here is the resulting custom column: