Calculate working days between two dates in Power Query
How-to · By Tahir Nas · Examples run in our simulator · Updated
11 min read
Power Query has no NETWORKDAYS function, so you count working days yourself: list every date from the start to the end, keep the ones that are weekdays and not holidays, and count what is left. Three standard functions do the work: List.Dates, Date.DayOfWeek and List.Count. Wrapped in a small custom function, it behaves like the Excel and DAX function, holidays included.
The definition to match
Microsoft's documentation for DAX NETWORKDAYS states the behaviour most people expect: it returns "the number of whole workdays between two dates (inclusive)", and weekend days and listed holidays don't count (NETWORKDAYS function, Microsoft Learn). So a Monday-to-Friday span with no holidays is 5, and a span that starts and ends on the same working day is 1.
Decide early whether your measure is "working days in the range" (inclusive, like NETWORKDAYS) or "working days elapsed" (often the inclusive count minus 1, so a ticket closed on the day it opened scores 0). Both are reasonable. Mixing them is how two reports end up disagreeing by one day per row.
The basic count
This query counts working days from Friday 19 December 2025 to Monday 5 January 2026, skipping weekends and three UK bank holidays.
let
Holidays = {#date(2025, 12, 25), #date(2025, 12, 26), #date(2026, 1, 1)},
WorkingDays = (startDate as date, endDate as date) as number =>
let
days = List.Dates(startDate, Duration.Days(endDate - startDate) + 1, #duration(1, 0, 0, 0)),
working = List.Select(days, each Date.DayOfWeek(_, Day.Monday) < 5 and not List.Contains(Holidays, _))
in
List.Count(working)
in
WorkingDays(#date(2025, 12, 19), #date(2026, 1, 5))Result
9
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The answer is 9. The range has 18 dates and 12 weekdays, and three of those weekdays are Christmas Day, Boxing Day and New Year's Day.
How it works:
- Subtracting two dates gives a duration, and
Duration.Daysturns it into a whole number of days (17 here). Adding 1 includes the end date. The Duration.Days reference has more examples. - List.Dates (Microsoft Learn) builds a list of dates from a start, a count and a step.
Date.DayOfWeek(_, Day.Monday)returns 0 for Monday through 6 for Sunday, so< 5means Monday to Friday (Date.DayOfWeek, Microsoft Learn). Always pass the second argument: without it, the documentation says a culture-dependent default is used. The ISO week number function relies on the same call.
Apply it to a table, with holidays and missing dates
Real data has open tickets with no close date, rows where the dates are the wrong way round, and holidays that live in their own table. This version handles all three.
let
Holidays = #table(type table [Date = date, Name = text], {
{#date(2025, 12, 25), "Christmas Day"},
{#date(2025, 12, 26), "Boxing Day"},
{#date(2026, 1, 1), "New Year's Day"},
{#date(2026, 4, 3), "Good Friday"},
{#date(2026, 4, 6), "Easter Monday"}
}),
HolidayList = List.Buffer(Holidays[Date]),
WorkingDays = (startDate as nullable date, endDate as nullable date) as nullable number =>
if startDate = null or endDate = null then null
else
let
first = List.Min({startDate, endDate}),
last = List.Max({startDate, endDate}),
days = List.Dates(first, Duration.Days(last - first) + 1, #duration(1, 0, 0, 0)),
working = List.Select(days, each Date.DayOfWeek(_, Day.Monday) < 5 and not List.Contains(HolidayList, _)),
sign = if endDate < startDate then -1 else 1
in
sign * List.Count(working),
Tickets = #table(type table [Ticket = text, Opened = date, Closed = nullable date], {
{"T-101", #date(2025, 12, 22), #date(2025, 12, 29)},
{"T-102", #date(2025, 12, 24), #date(2026, 1, 2)},
{"T-103", #date(2026, 4, 2), #date(2026, 4, 7)},
{"T-104", #date(2026, 4, 4), #date(2026, 4, 5)},
{"T-105", #date(2026, 1, 9), null}
}),
Added = Table.AddColumn(Tickets, "WorkingDays", each WorkingDays([Opened], [Closed]), Int64.Type)
in
AddedResult
| Ticket | Opened | Closed | WorkingDays |
|---|---|---|---|
| T-101 | 2025-12-22 | 2025-12-29 | 4 |
| T-102 | 2025-12-24 | 2026-01-02 | 5 |
| T-103 | 2026-04-02 | 2026-04-07 | 2 |
| T-104 | 2026-04-04 | 2026-04-05 | 0 |
| T-105 | 2026-01-09 | null | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The results: T-101 has 4 working days, T-102 has 5, T-103 has 2 (Good Friday and Easter Monday fall inside it), T-104 opened and closed on a weekend so it has 0, and the open ticket T-105 returns null.
Some choices in that function are worth explaining:
- Nulls stay null. An open ticket has no duration yet, and 0 would wrongly count it as instant. The lesson on null, empty text and errors explains why the difference matters.
- Reversed dates give a negative number rather than an error or a silent 0, so data entry mistakes show up in a filter. If you would rather stop the refresh, raise an error instead.
List.Buffergives every row the same stable, in-memory list of holiday dates to check (List.Buffer, Microsoft Learn). The post on when buffering helps and when it hurts explains why a lookup list like this is a good case.
If you count "days to close" rather than days in the range, subtract 1 from any count above 0.
Where the holiday list comes from
Holidays are data, not logic. Keep them in a table that someone other than the query author can update: an Excel table, a CSV on SharePoint, or a published source. For England and Wales, GOV.UK publishes bank holidays on its UK bank holidays page, and the same data is available as JSON at https://www.gov.uk/bank-holidays.json with separate lists for England and Wales, Scotland and Northern Ireland. In Excel or Power BI you can read it directly:
let
Source = Json.Document(Web.Contents("https://www.gov.uk/bank-holidays.json")),
Events = Source[#"england-and-wales"][events],
Holidays = Table.FromRecords(Events),
Typed = Table.TransformColumnTypes(Holidays, {{"date", type date}})
in
TypedThe simulator can't call the web, so here is the same shape with three events pasted in as text. It returns a table with title and date columns, and the dates convert cleanly because the feed uses yyyy-mm-dd.
let
Json = "{""england-and-wales"": {""division"": ""england-and-wales"", ""events"": [{""title"": ""Christmas Day"", ""date"": ""2025-12-25""}, {""title"": ""Boxing Day"", ""date"": ""2025-12-26""}, {""title"": ""New Year's Day"", ""date"": ""2026-01-01""}]}}",
Source = Json.Document(Json),
Events = Source[#"england-and-wales"][events],
Holidays = Table.FromRecords(Events),
Typed = Table.TransformColumnTypes(Holidays, {{"date", type date}})
in
TypedResult
| title | date |
|---|---|
| Christmas Day | 2025-12-25 |
| Boxing Day | 2025-12-26 |
| New Year's Day | 2026-01-01 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Company closure days (the week between Christmas and New Year, for example) are not on any public list. Append them to the bank holidays from your own table. The calendar table with closure days download has a worked example of flagging closures in a date table.
A different weekend
Not every organisation works Monday to Friday. The neat way to move the weekend is the second argument of Date.DayOfWeek: choose the day after your weekend as the first day of the week, and the working days become 0 to 4. For a Friday and Saturday weekend, Sunday is day 0 and Thursday is day 4.
let
WorkingDays = (startDate as date, endDate as date) as number =>
List.Count(List.Select(
List.Dates(startDate, Duration.Days(endDate - startDate) + 1, #duration(1, 0, 0, 0)),
each Date.DayOfWeek(_, Day.Sunday) < 5)),
Source = #table(type table [From = date, To = date], {{#date(2026, 3, 1), #date(2026, 3, 14)}}),
Added = Table.AddColumn(Source, "SunToThu", each WorkingDays([From], [To]), Int64.Type)
in
AddedResult
| From | To | SunToThu |
|---|---|---|
| 2026-03-01 | 2026-03-14 | 10 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Sunday 1 March to Saturday 14 March 2026 has 10 Sunday-to-Thursday days. For a one-day weekend or an irregular pattern, list the working weekday numbers instead and test with List.Contains({0, 1, 2, 3, 4, 5}, Date.DayOfWeek(_, Day.Monday)), which keeps Monday to Saturday. DAX NETWORKDAYS offers the same flexibility through its weekend codes, so check which code a model uses before you compare results.
Add working days to a date
The reverse question ("what date is five working days after this one?", the Excel WORKDAY function) uses the same building blocks. Generate enough candidate dates, keep the working ones, and pick the nth.
let
Holidays = {#date(2025, 12, 25), #date(2025, 12, 26), #date(2026, 1, 1)},
IsWorking = (d as date) as logical => Date.DayOfWeek(d, Day.Monday) < 5 and not List.Contains(Holidays, d),
AddWorkingDays = (startDate as date, n as number) as date =>
let
candidates = List.Dates(Date.AddDays(startDate, 1), n * 2 + 21, #duration(1, 0, 0, 0)),
working = List.Select(candidates, IsWorking)
in
working{n - 1},
Orders = #table(type table [Order = text, Received = date], {{"A", #date(2025, 12, 19)}, {"B", #date(2025, 12, 23)}, {"C", #date(2025, 12, 31)}}),
Due = Table.AddColumn(Orders, "DueIn5WorkingDays", each AddWorkingDays([Received], 5), type date)
in
DueResult
| Order | Received | DueIn5WorkingDays |
|---|---|---|
| A | 2025-12-19 | 2025-12-30 |
| B | 2025-12-23 | 2026-01-02 |
| C | 2025-12-31 | 2026-01-08 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
An order received on 19 December 2025 is due on 30 December, one received on 23 December is due on 2 January 2026, and one received on 31 December is due on 8 January. The candidate list of n * 2 + 21 days leaves room for weekends plus up to two weeks of closures, even when n is 1. If your closures can be longer, make it bigger, or the {n - 1} lookup fails with "There weren't enough elements in the enumeration", which the error page for that message explains.
Faster for large tables: a working-day number
Listing every date for every row is fine for a few thousand rows. For a fact table with hundreds of thousands of rows, it repeats the same work millions of times. A calendar with a running count of working days turns each row into a lookup and a subtraction.
let
Holidays = {#date(2025, 12, 25), #date(2025, 12, 26), #date(2026, 1, 1)},
Dates = List.Dates(#date(2025, 12, 1), 62, #duration(1, 0, 0, 0)),
Flags = List.Buffer(List.Transform(Dates, each if Date.DayOfWeek(_, Day.Monday) < 5 and not List.Contains(Holidays, _) then 1 else 0)),
DayCount = List.Count(Flags),
Running = List.Generate(
() => [i = 0, total = Flags{0}],
each [i] < DayCount,
each [i = [i] + 1, total = [total] + Flags{[i] + 1}],
each [total]
),
Calendar = Table.TransformColumnTypes(
Table.FromColumns({Dates, Flags, Running}, {"Date", "IsWorkingDay", "WorkdayNumber"}),
{{"Date", type date}, {"IsWorkingDay", Int64.Type}, {"WorkdayNumber", Int64.Type}}
),
Tickets = #table(type table [Ticket = text, Opened = date, Closed = date], {
{"T-101", #date(2025, 12, 22), #date(2025, 12, 29)},
{"T-102", #date(2025, 12, 24), #date(2026, 1, 2)},
{"T-106", #date(2025, 12, 27), #date(2026, 1, 5)}
}),
WithStart = Table.NestedJoin(Tickets, {"Opened"}, Calendar, {"Date"}, "S", JoinKind.LeftOuter),
S = Table.ExpandTableColumn(WithStart, "S", {"IsWorkingDay", "WorkdayNumber"}, {"StartIsWorking", "StartNumber"}),
WithEnd = Table.NestedJoin(S, {"Closed"}, Calendar, {"Date"}, "E", JoinKind.LeftOuter),
E = Table.ExpandTableColumn(WithEnd, "E", {"WorkdayNumber"}, {"EndNumber"}),
Result = Table.AddColumn(E, "WorkingDays", each [EndNumber] - [StartNumber] + [StartIsWorking], Int64.Type),
Tidy = Table.SelectColumns(Result, {"Ticket", "Opened", "Closed", "WorkingDays"})
in
TidyResult
| Ticket | Opened | Closed | WorkingDays |
|---|---|---|---|
| T-101 | 2025-12-22 | 2025-12-29 | 4 |
| T-102 | 2025-12-24 | 2026-01-02 | 5 |
| T-106 | 2025-12-27 | 2026-01-05 | 5 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
WorkdayNumber counts the working days from the start of the calendar up to and including each date. List.Generate carries the total forward from one day to the next, reading from a buffered list of flags. The post on List.Generate loops explains the pattern step by step. The calendar has one row per day, so it stays small even when it covers several years. The difference between the end and start numbers counts working days after the start date, and adding StartIsWorking puts the start date back in when it is itself a working day. T-101 and T-102 match the earlier function (4 and 5). T-106 opens on a Saturday and still gets the right answer, 5.
In a real model this calendar is your date table, extended with the two extra columns. The guide to building a date table in Power Query shows how to generate it from your data's first and last dates, and the lesson on merge join kinds covers the left outer joins used here. Make the calendar cover every date that can appear, or the merge returns null.
Check your numbers
A few test rows catch most mistakes, so keep them in a small table next to the query:
- A Monday-to-Friday week with no holidays returns 5.
- A single working day returns 1, and a single Saturday returns 0.
- A range is one lower for each weekday holiday it contains.
- A missing end date returns null, not 0 and not an error.
The lesson on date parts and durations covers the duration arithmetic behind all of this, and the numbers and dates exercises give you more practice. Every query above was run in the Power Query Exercises simulator, except the GOV.UK web call, which needs Excel or Power BI.
Frequently asked questions
Is there a NETWORKDAYS function in Power Query M?
No. The M function library has no working-day function. DAX in Power BI does have NETWORKDAYS, so if the calculation only feeds a measure you can do it there. Doing it in Power Query is the better choice when the result is a column you filter, group or export.
Why is my count one higher than I expected?
The + 1 makes the range inclusive of both dates, matching NETWORKDAYS. If you want "working days elapsed", subtract 1 from positive results, and decide what a same-day close should score.
Can half-day holidays be handled?
Give each date a weight instead of a 1 or 0 flag (1 for a full day, 0.5 for a half day, 0 for a holiday), and sum the weights with List.Sum instead of counting. The calendar approach works the same way with a running sum of weights.
Sources
- NETWORKDAYS function, Microsoft Learn (learn.microsoft.com)
- List.Dates (Microsoft Learn) (learn.microsoft.com)
- Date.DayOfWeek, Microsoft Learn (learn.microsoft.com)
- List.Buffer, Microsoft Learn (learn.microsoft.com)
- UK bank holidays page (gov.uk)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- Fiscal years and fiscal quarters in Power QueryAdd fiscal year, quarter and month columns in Power Query for any start month, label them FY25/26 style, and handle a tax year that starts on 6 April.
- Convert Unix timestamps and Excel serial dates in Power QueryTurn Unix epoch seconds or milliseconds and Excel serial numbers like 46023 into dates in Power Query, with tested M for text, nulls and 1904 workbooks.
- Build a monthly HR headcount report with Power QueryTurn an employee list with start and leave dates into month-end headcount, FTE by department, and starters and leavers that reconcile, using Power Query.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.