To subtract dates in Excel Power Query, use the Duration.Days function on the difference between two date columns, or simply select both columns and choose Subtract from the Add Column tab. For example, with columns named StartDate and EndDate, add a custom column with the formula Duration.Days([EndDate] - [StartDate]) to get the whole number of days between them. This article explains the exact steps, the difference between date and datetime values, common pitfalls, and how to handle business-day calculations.
Why Use Power Query for Date Subtraction?
Power Query (also called Get & Transform Data) is Excel’s built-in data preparation tool. Unlike regular worksheet formulas, Power Query lets you clean and transform data before it ever enters your spreadsheet. Subtracting dates in Power Query is useful when you import data from external sources—like a database, a CSV file, or a folder of files—and need to compute durations, ages, or deadlines as part of your data cleaning. The result is a repeatable query: if your source data updates, you refresh the query and the date difference recalculates automatically without redoing manual steps.
Step-by-Step: Subtract Two Date Columns
Here is the standard method for subtracting two date columns in Power Query (available in Excel for Microsoft 365, Excel 2021, and Excel 2016+ on Windows and Mac).
- Load your data into Power Query: select your data range or table, then go to Data > From Table/Range (or Data > From Sheet in newer versions).
- Ensure both columns are set to the Date or Date/Time data type. If they are text, select each column and choose Date from the Data Type dropdown in the Home tab.
- Go to the Add Column tab and click Custom Column.
- In the Custom Column dialog, give the new column a name (e.g., “DaysBetween”).
- Enter the formula:
Duration.Days([EndDate] - [StartDate])(replaceEndDateandStartDatewith your actual column names). - Click OK. The new column will show the number of days as a whole number.
- Click Close & Load to bring the results back to a worksheet.
Alternatively, you can use the Subtract button on the Add Column tab: select the two date columns (hold Ctrl to select both), then click Add Column > From Date > Subtract. That creates a Duration column showing days, hours, minutes, etc. To get just days, you can then change the data type to Whole Number or use the formula above.
Understanding Date vs. DateTime and Durations
In Power Query, a Date value stores only the date (e.g., 2025-03-15), while a Date/Time value includes time (e.g., 2025-03-15 14:30). When you subtract two date values, Power Query returns a Duration value, which is represented as a number of days, hours, minutes, and seconds. For example, subtracting #date(2025,1,1) from #date(2025,1,10) gives a duration of 9 days. The Duration.Days function extracts just the whole-day component from that duration. If your columns include time, the result may include fractions of a day. For instance, subtracting #datetime(2025,1,1,0,0,0) from #datetime(2025,1,2,12,0,0) gives 1.5 days, and Duration.Days would return 1 (truncating the fraction). To get a precise decimal, you can use Duration.TotalDays instead, which returns the total number of days as a decimal number.
Common Problems and How to Fix Them
Several issues often trip up users when subtracting dates in Power Query.
- Text columns that look like dates: If your columns are text, Power Query cannot subtract them. Convert them to Date/Time using the Data Type dropdown. If conversion fails, check for inconsistent formats (e.g., “Jan 5, 2025” vs. “2025-01-05”) and use Replace Values or Parse Date to clean them first.
- Null values: If either date is null, the subtraction returns null. You can handle this with a conditional column or use
tryin a custom column:try Duration.Days([EndDate] - [StartDate]) otherwise null. - Negative results: If the start date is later than the end date, the duration will be negative. That is mathematically correct; decide whether you want to take the absolute value using
Number.Abs. - Time portion causing unexpected values: If your dates include times,
Duration.Daysmay return a value one less than expected when the end time is earlier in the day than the start time. For example, from Jan 1 23:00 to Jan 2 01:00 is only 2 hours, soDuration.Daysreturns 0. If you want calendar-day difference, strip the time first by usingDate.From([EndDate])andDate.From([StartDate])in the formula.
How to Subtract Dates to Get Days, Months, or Years
Sometimes you need months or years, not just days. Power Query has functions for that: Date.Year, Date.Month, Date.Day, and Date.AddDays, but no direct “DATEDIF” equivalent. To get the number of whole months between two dates, you can use a custom formula like this (assuming columns StartDate and EndDate):
(Date.Year([EndDate]) - Date.Year([StartDate])) * 12 + (Date.Month([EndDate]) - Date.Month([StartDate]))
This counts the month difference ignoring days, which is similar to Excel’s DATEDIF with “m” but does not adjust for day-of-month. For years, use Date.Year([EndDate]) - Date.Year([StartDate]). For a more precise “age in years” that accounts for birthdays, you might use a combination of Date.AddYears and a conditional, but that gets complex. For most purposes, subtracting dates in days is the most common and straightforward operation.
Business Days and Working Days Calculation
Subtracting calendar days is simple, but many business scenarios require counting only weekdays (Monday–Friday). Power Query does not have a built-in NETWORKDAYS function, but you can create a list of dates between the two dates and count weekdays. Here is a custom column formula that returns the number of weekdays between two date columns (assuming StartDate and EndDate):
let
Start = [StartDate],
End = [EndDate],
AllDates = List.Dates(Start, Duration.Days(End - Start) + 1, #duration(1,0,0,0)),
Weekdays = List.Select(AllDates, each Date.DayOfWeek(_, Day.Monday) < 5)
in
List.Count(Weekdays) - 1
This creates a list of every date from Start to End (inclusive), filters to Monday–Friday (DayOfWeek returns 0 for Monday, 4 for Friday), counts them, and subtracts 1 to exclude the start day if you want the number of full working days between the two dates. If you need to exclude holidays, you would need a reference table of holiday dates and filter those out as well. This formula is advanced; for simple day counts, stick with Duration.Days.
Comparing Power Query Date Subtraction to Excel Formulas
In a regular worksheet, you would subtract dates with a simple formula like =B2-A2 (where B2 is the end date and A2 is the start date) and format the result as a number. That gives the same day count. Power Query offers advantages when your data is messy or comes from an external source: you can automate the transformation, handle errors, and refresh without redoing steps. However, for a one-off calculation in a small dataset, a worksheet formula is faster and simpler. Power Query is also different from Excel’s DATEDIF function, which can return months or years but is not in Power Query. If you need to use the result in further calculations, Power Query results are static once loaded unless you refresh; worksheet formulas recalculate live.
Frequently Asked Questions
How do I subtract two dates in Power Query to get the number of days?
Use the custom column formula Duration.Days([EndDate] - [StartDate]) as described above. Make sure both columns are date type, and the result will be a whole number of days.
Why is my Power Query date subtraction returning a negative number?
Because the start date is later than the end date. The duration is negative by design. If you want a positive difference regardless of order, wrap the formula in Number.Abs: Number.Abs(Duration.Days([EndDate] - [StartDate])).
Can I subtract dates in Power Query if my columns include time?
Yes, but the result may include fractional days. To get whole days, use Duration.TotalDays and then round, or first convert both columns to date-only using Date.From in the formula: Duration.Days(Date.From([EndDate]) - Date.From([StartDate])).
Does Power Query have a DATEDIF function like Excel?
No, Power Query does not have DATEDIF. You can achieve month or year differences using Date.Year and Date.Month as shown in the section above, but for days, Duration.Days is the direct equivalent.
How do I count business days between two dates in Power Query?
Use the custom column formula provided in the business days section. It builds a list of dates and filters for weekdays. For holidays, you need to add a filter against a holiday table.
What if my date columns have errors or nulls?
Power Query will return null for rows with null dates. To handle errors, use a try expression: try Duration.Days([EndDate] - [StartDate]) otherwise null. You can also use Replace Errors from the Transform tab to replace error values with a default.
Related
- Excel Month of Date as Text: 5 Easy Ways
- Free Excel Mileage Log Templates: 7 Real Options
- Excel Lowercase: Convert Text to Lowercase
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Validation From a Table: Setup and FixesYes. In Excel for Microsoft 365 on Windows, you can use a table column to supply choices for…
- How to Add Automatic Page Numbers in Microsoft WordTo add automatic page numbers in Word, open the Insert tab, choose Page Number, select where the numbers…
- Excel Data Analysis: How to Build a Useful ModelYes—Excel can model data for analysis using structured tables, formulas, PivotTables, charts, and, where available, the Data Model.…
- Excel Cell Merge Shortcut Keys and How to Use ThemIn Excel for Microsoft 365 on Windows, select the cells and press Alt+H, M, then C to use…
- How to Change Strikethrough Color in Excel Cells EasilyExcel does not let you set a strikethrough line to a different color from the text. You can…
- Search an Excel Column for Matching TextTo search an Excel column for matching text, press Ctrl+F on Windows, enter the text, and choose Find…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.