Date.Networkdays function for Power Query and Power BI

Today I’m going to share my custom NETWORKDAYS function for Power Query with you that uses the same syntax than its Excel-equivalent.

NETWORKDAYS function

This function’s first 3 parameters work just like the Excel function and there is a 4th parameter that allows adjusting the day on which the week shall start:

  1. Start as date
  2. End as date
  3. optional holidays as list of dates
  4. optional number to change the start of the week from Monday (default: 1) to any other day (2 would mean that the week starts on Tuesday instead)

The function comes with a UI that lets you first choose a table containing the holidays and then choose the column with the holiday dates.

Date.Networkdays function for Power Query and Power BI

UI for NETWORKDAYS function for Power Query

Date.Networkdays function for Power Query and Power BI

Select date column for NETWORKDAYS function

But you can also type in the list of holidays in manually. Therefore leave the optional parameter blank if you use it through the UI and edit the formula afterwards like so:

fnNETWORKDAYS ( StartDate, EndDate, {#date(2020, 1, 1) {#date(2020,12,25)} ), adding all necessary dates into the 3rd parameters list.

The Code

// fnNETWORKDAYS
let func =
(StartDate as date, EndDate as date, optional Holidays as list, optional StartOfWeek as number) =>
let
// optional StartOfWeek, if empty the week will start on Monaday
startOfWeek = if StartOfWeek = null then 1 else StartOfWeek,
// Providing for logic where EndDate is after StartDate
Start = List.Min({StartDate, EndDate}),
End = List.Max({StartDate, EndDate}),
// Switch sign if EndDate is before StartDate
Sign = if EndDate < StartDate then -1 else 1,
// Get list of dates between Start- and EndDate
ListOfDates = List.Dates(Start, Number.From(End – Start) + 1,#duration(1,0,0,0)),
// if the optional Holidays parameter is used: Keep only those dates in the list that don't occur in the list of Holidays;
// otherwise continue with previous table
DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, List.Transform(Holidays, Date.From )),
// Select only the first 5 days of the week
// The 1 in the 2nd parameter of Date.DayOfWeek makes sure that Monday will be taken as first day of the week
DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_, startOfWeek) < 5 ),
// Count the number of days (items in the list)
CountDays = List.Count(DeleteWeekends) * Sign
in
CountDays ,
documentation = [
Documentation.Name = " Date.Networkdays.pq ",
Documentation.Description = " Returns the number of whole working days between StartDate and EndDate similar to the NETWORKDAYS-function in Excel. Working days exclude weekends and any dates identified in holidays. ",
Documentation.LongDescription = " Returns the number of whole working days between StartDate and EndDate similar to the NETWORKDAYS-function in Excel. Working days exclude weekends and any dates identified in (optional) holidays. ",
Documentation.Category = " Date ",
Documentation.Source = " www.TheBIccountant.com https://wp.me/p6lgsG-2fA . ",
Documentation.Version = " 2.1 Catering for negative duration",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description = " ",
Code = " ",
Result = " "]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

view raw
Date.Networkdays.pq
hosted with ❤ by GitHub

Twists

If your holidays don’t sit in a dedicated table but in separate column of a calendar table like so:

Holiday as a column within a complete Calendar table

I’d recommend to reference that table, filter only holidays and then reference it’s data column like mentioned before.

Enjoy & stay queryious 😉

Comments (53) Write a comment

  1. I appreciate you sharing the code. I’m having an issue with it not recognizing the parameters when I paste the code into a blank query to create the function.

    Reply

    • Sorry, but I don’t understand the issue: What do you mean with “not recognizing the parameters?”
      If you paste the code into a blank query and press OK, that query should be converted to a function.
      You then reference that function in your further queries.
      Otherwise please try to give some more details, thanks.

      Reply

    • Thank you thank you Imke! For those that were having trouble copying the code into a function there are a couple of caveats I learned the heard way:

      You have to be in import, not direct query mode. That sent me back to the drawing board.
      In Power Query Editor, click “New Source” and then “Blank Query”
      Once in the blank query, click “Advanced Editor” from the Query ribbon
      Paste your code and click done. The parameters window shown above will pop up.

      Reply

  2. Hello Imke,
    Thank you for the awesome function. I am having one problem though.
    My end date is in a different query so that it can be updated monthly without opening the Edit Query window.
    Is there a way to reference the other query from your function without triggering the Formula.Firewall error?

    Thank you.

    Reply

    • Hi Matthew,
      you can try to functionize the reference.

      Create a new query (YourNewFunctionQuery) that reference the DifferentQuery. Navigate to the “end date”.
      Go to the advanced editor and put ” ()=> ” in front of the code in there.
      That will transform it into a function (without a parameter).

      You can invoke it with empty brackets like so: YourNewFunctionQuery()

      /Imke

      Reply

  3. Hello Imke,
    Thank you for the advice. I successfully transformed the reference to a function.

    However, when referencing from your function, it fails with the same error as before.
    “Formula.Firewall: Query ‘spend_calc’ (step ‘Invoked Custom Function’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”

    I think the problem is lazy evaluation used by PQ – referenced by Ken Puls (https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/), Chris Webb, and others.
    Therefore, we can’t reference a different query using a custom function.

    I ended up building the referenced query (which just pulled month start date) into the main query to pull in the data and subtracting the end date from start date. I still don’t have weekends and holidays removed though.

    Purely for experimentation, I tried changing your function data type to list for the first two parameters. This lets me choose a column in the current query as start date and a different column as end date. Unfortunately, changing your function data types caused a different error, “Expression.Error: We cannot convert a value of type List to type Date.”

    If there is a way to adjust your function to accept a List instead of a date, that would be ideal. I need it to not have a fixed start or end date, but accept the date from an existing column in the current query.

    Apparently, I just don’t know enough about M functions. I will study more.

    Thank you for your time.
    Matthew

    Reply

  4. Hi Imke,
    thanks so much for this, it’s very clear & what I have been looking for!

    The only challenge I have is that when calculating the difference between 2 date columns, Power Query returns my result with the format “1.00:00:00” instead of “1”

    This means I get this error when using the custom function:

    Expression.Error: The ‘increment’ argument is out of range.
    Details:1.00:00:00

    Reply

    • Sorry Scott, but I cannot follow.
      My function returns the result of a List.Count function. So that will always be a number.
      What are you referencing to that returns a duration?
      If you have a column with a start date and another with an end date you should feed them as the first 2 arguments into my function.
      /Imke

      Reply

    • @Scott I get the same thing. I have two columns with date (type date dd-mm-yy) and then for some records i get error

      Expression.Error: The ‘increment’ argument is out of range.
      Details:1.00:00:00

      i can not figure out what the problem is as it works for some rows and it worked on other ocasionas before even with the same columns

      @Imke I really like the function but for above reason I am not able to use it. Also the UI for the Holidays doesnt work for me.. Do I need to enable some preview functionality for this function to work ?

      Reply

    • @ Scot .. the function only works if Start Date < End Date so it is not entirely like NETWORKDAYS but the easies work around is to do something like
      if END DATE<START DATE
      then fnNETWORKDAYS(END DATE, START DATE, null, null)
      else
      if START DATE<END DATE
      then fnNETWORKDAYS(START DATE, END DATE, null, null)
      else 0
      //with the 0 I covered the issue i have when start date and end date are same date.

      @Imke If you have any ideas how to incorporate it into the function that way that i would be able to calculate the networkdays also if the end date is before start date .. your help would be very much appreciated.

      thank you
      m

      Reply

      • Hi Michal,
        thanks for making me aware of this.
        Have adjusted the function accordingly.
        /Imke

        Reply

        • hi @Imke I really like your “adjustment” i will test it but from the code it looks clever.

          I have another question suggestion. In logistics when start and end date is the same it is considered as same day delivery and the value should be 0 , another example next day delivery is lets say start day We 6th of May end day Th 7th of May with NETWORKDAYS in excel and also your M version, this would give me value of 2, but i would like the value to be 1.

          The easiest solution that comes to my mind is just substract 1 or – 1 based on the fact if the result is negative or positive number. But that would make the step for that column complex to read …. is there maybe a way that this could be build in your function or to be added as an optional parameter??

          Thank you .. really appreciate your help

          m.

          Reply

          • Hi michal,
            I’d prefer to keep this formula in accordance with the Excel equivalent.
            /Imke

  5. Hi @Imke .. Ok .. fair point.. 🙂 I have one question/feedback for the optional parameter holidays .. when try to add column using this function .. and click on the select column for Holidays(optional) the UI doesn’t offer me any options .. Is it something that i am doing wrong ? I actually get it to work one or two times but then i just get no options even though i have there several tables/query… what I am trying to accomplish is the previous scenario where the parameters for your function would be something like (startdate, enddate, startdate ->as the holiday optional parameter, null) .. you can do it in excel so that’s why i was trying to experiment with this logic but i can not get that option to work at all..
    on another note can you recommend a good solution how to deal with the holidays where i would like to use table with multiple columns (holidays for different countries) and then use your function with some kind of if statement that would look up the country and then remove the holidays accordingly ..

    thank you

    /m

    Reply

    • Hi Michal,
      no idea why that dialogue doesn’t show up. But you can simply reference it by using some code like so: #”YourTableName”[YourColumnName]
      As for a table with multiple columns for each country: You can unpivot it so all values with in one column. Or if it is a table with just holidays and you want all columns to be considered, use List.Combine(Table.ToColumns(#”YourTableName”)).
      This will first turn your table into a list of nested lists (for each column) and then combine them all to on big list.
      /Imke

      Reply

        • Hi Jerry,
          the dropdown won’t operate if there are no tables in your file yet.
          Otherwise I cannot imagine what the reason could be.
          /Imke

          Reply

        • I had this issue until I promoted the function definition to the top of the Queries list, then it was able to see all the tables. Thanks Imke, this function is very useful!

          Reply

    • Yes, English is not my first language, so you will probably find many of them in my posts.
      I decided to focus on the technical aspects and don’t invest too much time in language polishing.

      Reply

  6. Hello
    For me, the working days are Sunday to Thursday
    And Friday and Saturday are days of rest
    How can I change this?

    Reply

  7. Hi Imke,

    I’ve use a similar query, but simplified.
    It has a terrible affect on the query loading time. Now it’s about 15-20′ where before it was 1-2′.
    The table has about 12000 rows and is a dataflow table merged with a table from the datawarehouse and the productiondays table also houses in the datawarehouse.

    Do you have any toughts on the performance issue?
    In the past I tried the same on two tables housing in the datawarehouse, hoping to use query folding. But i had to stop because of performance issues.

    I have a lot of lead times to calculate and was hoping to do is in power query instead of dax because of performance.

    let
    WorkDaysBetween = (start as date, end as date, dimdate as table) =>
    Table.RowCount(
    Table.SelectRows(
    dimdate
    ,each
    ( ([Date] >= start and [Date] <= end)
    and [WorkDayFlag] = true )
    )
    )
    in
    WorkDaysBetween

    Reply

    • Hi Kris,
      One of the reasons is that your query references another table (“dimdate”) in each row of your table. Even without buffering that table first, you should expect that to be slower than mine.
      So going with my solution should run fast. You can create a list of holidays like so: List.Buffer(List.Select(dimdate[WorkDayFlag], each _ <> true))
      /Imke

      Reply

  8. Well done and thank you so much for this. Really great work for such important issue on PowerBI.

    May I ask:

    Is there anything I could do to adjust the code, in order to use as EndDate today’s date? I always have a “Start Date” but not always a end date and that gives me an error.

    Thank you

    Reply

    • Hi Terry,
      please try changing the code in row 10 to this:

      End = List.Max({StartDate, EndDate, Date.From(DateTime.LocalNow())})

      /Imke

      Reply

  9. This. Is. So. Awesome. Thank you. Is there any way to handle null dates in either the start or end dates?

    Reply

  10. This is amazing. Is there a way to use this function to calculate the end date? Start date + 3 network days = end date?

    Reply

    • Hi Sarah,
      yes, that’s possible. I will write a blogpost for it shortly.
      Wondering if there is an equivalent function in Excel or elsewhere?
      Would like to give that new function a meaningful name.
      Thanks, Imke

      Reply

  11. Thank you for this function. Just wanted to check how can we apply this for two custom columns.

    Reply

  12. Here is where this function varies from NETWORKDAYS in Excel and what is causing me a problem. If the start date is on a weekend or holiday, this function treats the next business day as day zero in the count whereas NETWORKDAYS counts the next business day as 1.

    As a real world example, I have tickets within my data set that were opened on 15 Feb 2021, which was a holiday in the USA. Using a list of holidays and the NETWORKDAYS formula, Excel calculates today (17 Feb 2021) as the second business day since the ticket was opened. This function seems to adjust the start date to 16 Feb 2021 because of the holiday, so it calculates today as the first business day since the ticket was opened.

    I’ve tried several ways to account for this, but all of them result in various issues. Is there a way to force the function to use the actual start date in the data even when that date is a holiday or weekend?

    Reply

      • Thank you for the quick reply, Imke.

        You are right, of course. I invoked the function in my workbook just to make sure I wasn’t crazy and it still returned 1 day using the same date/holiday parameters. I replaced all of the code in my function with the code in your spreadsheet and it then returned 2 days. I must have had an older version of the code or something as I didn’t get it directly from this site.

        Thanks again!

        Reply

  13. This is perfect and exactly what I was looking for! Thank you so much for making this and for sharing it! 🙂

    Reply

  14. Super handy, thanks!

    You can use the following if you need to use today’s date (like Today() in Excel):

    DateTime.Date(DateTime.LocalNow() as datetime)

    Reply

  15. I´ve been using this function in my queries for quite a while now, and it has been working great, but now I´m facing a little problem.

    The issue that I found is that I have two dates (March 9 as start date and March 10 as end date), and with the start of week value of 1, I´m getting a 2 days difference as an output, where the correct answer should be 1. I have not selected any holidays cause for me that isn´t necessary.

    it would be great if you could help me figure it out why I´m facing this problem.

    Best regards,

    Reply

  16. Hello Imke,
    I’m new to Power Query and I just can’t change the code to my use.

    Instead of giving me the number of networkdays, I want to change the function so it does the exact opposite: giving me the number of holidays, Saturdays and Sundays between 2 dates.
    How can I do that?

    Thank you for your help.

    Reply

  17. Hello!
    Is there also a version of this code available in case I would need to implement 2 or more public holidays tables? And what would be the correct approach if I would like the DAX script to check on a specific column in the table if a predefined word is present?

    The goal would be that if the word “LUX” appears in a predefines column, the public holiday calendar for Luxemburg should be used, for this specific row if not the public holidays calendar for Germany.

    As my DAX skills are extremely limited, I would like to ask for your help 😀

    Thank you in advance!

    Reply

    • Hi Eugene,
      if my understanding is correct, it should work like so:
      fnNETWORKDAYS ( StartDate, EndDate, if Text.Contains([YourPredefinedColumn], “LUX”) then HolidayCalendarLux[DateField] else OtherHolidayCalendar[DateField] )
      Cheers,
      Imke

      Reply

  18. Hi there,

    Thank you for this amazing function. It has been working great until I wanted to add the holiday table. Probably dumb question, but not sure what to put for the holidays field. I added the holiday table to the function UI.

    = Table.AddColumn(#”Removed Columns1″, “NWD”, each NWD([IQOpenDate], [#”ClosedDate_(NWD)”],{#”Firm Holidays”[FirmHoliday]}, null))

    Reply

    • Hi Dustin,
      I believe you have to omit the curly brackets then:
      = Table.AddColumn(#”Removed Columns1″, “NWD”, each NWD([IQOpenDate], [#”ClosedDate_(NWD)”],#”Firm Holidays”[FirmHoliday], null))

      Reply

  19. Hello Imke
    As always – amazing. Thank you so much for sharing.
    Do you have a tip for me on how to use the function to add a set number of workdays to a date?
    All best
    Dave White

    Reply

  20. Imke, Thank your for the networkdays function very nice. I have 1 issue though. My start date needs to be adjusted whenever i click on my fiscal month slicer. With your solution it is only showing me the correct number of workdays for the current month. How do I get the start date to move dynamically as i filter earlier months? No matter what i put in it states it can’t determine a single value for the start date field.

    Reply

    • Hi David,
      you can use this function only in the (Power) query editor and not as a DAX-function (which could react to slicer selections)
      /Imke

      Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz