Calculate Date Difference in Power Query (Days, Months, Years)

You can use Power Query’s built-in functions to calculate the difference between two dates.

I will show you how to use built-in functions in Power Query to calculate the difference between dates in terms of days, months, and years.

Calculate Days Between Two Dates

Suppose you have the Excel table below showing the start date and end date of various projects.

You want to calculate the duration of each project in days.

Here is how you can do it in Power Query:

Step #1: Load the Table Data into Power Query Editor

Use the steps below to load the table data into the Power Query Editor:

1. Select any cell in the table.
2. Open the Data tab and click the From Table/Range option on the Get & Transform Data group.

The above step loads the table data into the Power Query Editor.

Step #2: Add a Custom Column to the Table in the Power Query Editor

Use the steps below to add a custom column based on a custom formula to the table in the Power Query Editor:

1. Open the Add Column tab and click the Custom Column command button on the General group.

The above step opens the Custom Column dialog box.

2. Do the following on the Custom Column dialog box:
   • Enter ‘DurationInDays’ on the ‘New column name’ box.
   • Place the cursor after the equal sign on the ‘Custom column formula’ box and enter: 

=Duration.Days([EndDate] - [StartDate])

Your Custom Column dialog box should look like the one below.

3. Click OK.

The above step adds a custom column named ‘DurationInDays’ to the table, displaying the days it took to complete each project.

Step #3: Load the Query Results Back into Excel

Load the Query Data back into an Excel worksheet by clicking the Close & Load command button on the Home tab.

Power Query loads the results to the default worksheet.

Explanation of the Formula

= Duration.Days([EndDate] – [StartDate])

• [EndDate] – [StartDate] – This subtracts the start date from the end date, returning a duration value.
• Duration.Days(…) – This function extracts the number of whole days from the duration returned in the previous step.

Calculate Months Between Two Dates

Let’s say you have the Excel table below showing the start date and end date of various projects.

You want to calculate the number of months each project took.

Here is how you can do it in Power Query:

1. Load the table data into Power Query Editor as described in the previous section, ‘Calculate Days Between Two Dates.’
2. Open the Custom Column dialog box as described in the previous section, ‘Calculate Days Between Two Dates.’ Do the following on the dialog box:
   • Enter ‘DurationInMonths’ on the ‘New column name’ box.
   • Place the cursor after the equal sign on the ‘Custom column formula’ box and enter:

Date.Month([EndDate]) - Date.Month([StartDate]) + 12 * (Date.Year([EndDate]) - Date.Year([StartDate])).

Your Custom Column dialog box should appear like the one below.

4. Click OK.

The above step adds a custom column named ‘DurationInMonths’ to the table, displaying the months it took to complete each project.

Load the Query Data back into an Excel worksheet by clicking the Close & Load command button on the Home tab.

Explanation of the Formula

=Date.Month([EndDate]) – Date.Month([StartDate]) + 12 * (Date.Year([EndDate]) – Date.Year([StartDate]))

The formula calculates the number of complete months between two dates.

Here’s how it does it:

• Date.Month([EndDate]) – Date.Month([StartDate]) – This part calculates the difference in months within the same year. For example, if the start date is January 15, 2024, and the end date is March 10, 2024, this part returns 3-1 = 2.
• 12 * (Date.Year([EndDate]) – Date.Year([StartDate])) – This part calculates the number of years between the two dates and converts them into months by multiplying them by 12, since each year has 12 months. For example, if the start date is January 1, 2023, and the end date is March 1, 2024, this part would return 12 * (2024 – 2024) = 0.
• The formula adds the results of the two parts to give the total number of months between the two dates. In our example, the total number of months between the two dates is 2 + 0 = 2.

Calculate Years Between Two Dates

Let’s say you have the Excel table below containing the birth date and death date of some US presidents.

You want to calculate the years each president lived.

Here’s how to do it in Power Query:

1. Load the table data into Power Query Editor as described in the section, ‘Calculate Days Between Two Dates.’
2. Open the Custom Column dialog box as described in the section, ‘Calculate Days Between Two Dates.’ Do the following on the dialog box:
   • Enter ‘YearsLived’ on the ‘New column name’ box.
   • Place the cursor after the equal sign on the ‘Custom column formula’ box and enter:

=Date.Year([Died]) - Date.Year([Born])

Your dialog box should resemble the one below.

3. Click OK.

The above step adds a custom column named ‘YearsLived’ to the table, displaying the years each president lived.

Load the Query Data back into an Excel worksheet by clicking the Close & Load command button on the Home tab.

Explanation of the Formula

=Date.Year([Died]) – Date.Year([Born])

• Date.Year(…) – The Date.Year function extracts the year from the given date. For example, if the given date is January 9, 1913, the function extracts 1913.
• The formula subtracts the birth year from the death year, giving the total number of calendar years between the two dates.

I have shown you how to use Power Query’s built-in functions to calculate the difference between dates in days, months, and years. I hope you found the tutorial helpful.

Other Power Query and Excel articles you may also like:

• Getting Started with Power Query
• Remove Duplicates in Power Query
• Remove Null Values in Power Query
• Concatenate in Power Query (Columns, Text, Numbers)
• VLOOKUP in Power Query
• IF Then Else in Power Query
• Get Today’s Date in Power Query