Power Query Problems

How to Calculate Working Days Between Dates in Power Query

Jason Miller Oct 13, 2026 872 views

Question details

The user needs to calculate the number of net working days between a start date and an end date in Power Query, while excluding weekends and optionally a list of holidays.

How to Calculate Working Days Between Dates in Power Query

Product: Microsoft Excel / Power Query
Device & OS: not provided
Scenario: During data transformation, a networkdays calculation is required, but Power Query lacks a native Date.IsWeekend or Networkdays function.
Observed behavior: Formulas attempting to use Date.IsWeekend result in errors because the function is not recognized in the Power Query M language, requiring a custom list-generation approach instead.

Before you start

Ensure both your 'Start Date' and 'End Date' columns are properly formatted as Data Type: Date. If you plan to exclude holidays, load a table of holiday dates into Power Query, drill down the column to convert it to a List, and name the query exactly 'Holidays'.

Solution 1Recommended

Calculate Working Days Using a Custom Column Formula

Use a custom M code formula combining List.Dates and Date.DayOfWeek to manually filter out weekends and holidays.

Because Power Query does not have a built-in NETWORKDAYS function, we can achieve this by generating a list of every date between the Start Date and End Date. We then filter this list to keep only the dates that fall between Monday and Friday, and optionally remove any dates that appear in a predefined 'Holidays' list.

Open the Power Query Editor

Navigate to the Data tab in the Excel ribbon and click 'Get Data' > 'Launch Power Query Editor' to view your dataset.

Add a Custom Column

Select the 'Add Column' tab from the Power Query ribbon and click on the 'Custom Column' button.

Insert the Working Days M Code

In the Custom Column dialog, name the new column 'Working Days'. In the formula box, paste the following code: let startDate=[Start Date], endDate=[End Date], dates=List.Dates(startDate, Duration.Days(endDate-startDate)+1, #duration(1,0,0,0)), working=List.Select(dates, each Date.DayOfWeek(_, Day.Monday) < 5 and not List.Contains(Holidays, _)) in List.Count(working)

Confirm and Apply

Click 'OK' to save the custom column. Ensure the resulting column displays numerical values, then click 'Close & Load' on the Home tab to output the transformed data to your worksheet.

Calculate Working Days Using a Custom Column Formula

Handling Missing Holidays: If you do not need to account for holidays, simply remove 'and not List.Contains(Holidays, _)' from the formula to avoid query errors.

Solution 2

Create a Reusable Networkdays Custom Function

Define a custom M function (fxNetworkdays) in a blank query to easily calculate working days across multiple tables without repeating the lengthy code.

Free Microsoft Office alternative

Need a simpler way to calculate working days? Try WPS Office

If writing complex M code in Power Query seems daunting, you can easily calculate working days directly in your spreadsheet using WPS Office. WPS Spreadsheets features the built-in NETWORKDAYS function that handles weekends and holidays instantly, bypassing the need for advanced data modeling.

1. Download WPS Office: Install the free WPS Office suite on your device.
2. Open your Spreadsheet: Open your existing Excel workbook directly in WPS Spreadsheets.
3. Use NETWORKDAYS: Type =NETWORKDAYS(A2, B2, [Holidays]) in any cell to calculate working days without needing Power Query.

Built-in NETWORKDAYS function for instant business day calculationsFully compatible with Microsoft Excel (.xlsx) formats and standard formulasLightweight, lightning-fast, and completely free to useIntuitive familiar interface with virtually no learning curve

Frequently Asked Questions

Why do I get an error when using Date.IsWeekend in Power Query?

Power Query's M language does not include a native Date.IsWeekend function. To identify weekends, you must use Date.DayOfWeek to determine the numeric day of the week and filter out Saturdays (5) and Sundays (6) manually.

How can I handle situations where the end date is before the start date?

If your dataset includes end dates that precede start dates, the standard List.Dates function will return an error because it expects a positive duration. You must wrap your logic in an if-statement to check which date is greater, calculate the absolute difference to build the list, and then multiply the final count by -1 to indicate a negative timeframe.

How do I format the Holidays list so the custom column can use it?

Create a simple table in Excel containing your holiday dates and load it into Power Query. Right-click the header of the date column and select 'Drill Down' to convert it from a Table to a List. Name this query 'Holidays'. Your working days formula can then reference it directly using the List.Contains(Holidays, _) function.

Excel & Data Problems