Power QueryExercises

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.Days turns 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 < 5 means 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
    Added

Result

TicketOpenedClosedWorkingDays
T-1012025-12-222025-12-294
T-1022025-12-242026-01-025
T-1032026-04-022026-04-072
T-1042026-04-042026-04-050
T-1052026-01-09nullnull

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.Buffer gives 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
    Typed
Shown for Excel and Power BI Desktop only: this code needs a data source or language feature the simulator doesn't run, so no output is shown.

The 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
    Typed

Result

titledate
Christmas Day2025-12-25
Boxing Day2025-12-26
New Year's Day2026-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
    Added

Result

FromToSunToThu
2026-03-012026-03-1410

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
    Due

Result

OrderReceivedDueIn5WorkingDays
A2025-12-192025-12-30
B2025-12-232026-01-02
C2025-12-312026-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
    Tidy

Result

TicketOpenedClosedWorkingDays
T-1012025-12-222025-12-294
T-1022025-12-242026-01-025
T-1062025-12-272026-01-055

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:

  1. A Monday-to-Friday week with no holidays returns 5.
  2. A single working day returns 1, and a single Saturday returns 0.
  3. A range is one lower for each weekday holiday it contains.
  4. 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

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

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.