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:
- Start as date
- End as date
- optional holidays as list of dates
- 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.
UI for NETWORKDAYS function for Power Query
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // fnNETWORKDAYS | |
| let func = | |
| (StartDate, EndDate, 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, | |
| // cater for empty dates | |
| StartEmpty = if StartDate = null then #date(1900,1,1) else StartDate, | |
| EndEmpty = if EndDate = null then #date(1900,1,1) else EndDate, | |
| // Providing for logic where EndDate is after StartDate | |
| Start = List.Min({StartEmpty, EndEmpty}), | |
| End = List.Max({StartEmpty, EndEmpty}), | |
| // Switch sign if EndDate is before StartDate | |
| Sign = if EndEmpty < StartEmpty 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 = " 3.1 Catering for empty dates", | |
| Documentation.Author = " Imke Feldmann ", | |
| Documentation.Examples = {[Description = " ", | |
| Code = " ", | |
| Result = " "]}] | |
| in | |
| Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)) |
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.
If you are interested to see simplified variation of this approach with some nice tweals, please check out Working Days between Dates in Power Query – BI Gorilla.
Enjoy & stay queryious 😉
Thank you for sharing 🙂
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.
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.
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.
Thank you very much for sharing!
You saved us 🙂
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.
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
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
Hi Matthew,
unfortunately the firewall errors are a bit of a complex task: https://www.poweredsolutions.co/2019/03/12/data-privacy-and-the-formula-firewall/
I would have to examine the specific task on the basis of professional consulting services.
/Imke
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
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
@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 ?
@ 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
Hi Michal,
thanks for making me aware of this.
Have adjusted the function accordingly.
/Imke
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.
Hi michal,
I’d prefer to keep this formula in accordance with the Excel equivalent.
/Imke
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
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
I actually have the same issue – the dialogue shows up but the query dropdown doesn’t operate.
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
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!
Great function Imke. Spelling mistake in the first sentence “than” should be “as”.
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.
Hello
For me, the working days are Sunday to Thursday
And Friday and Saturday are days of rest
How can I change this?
Hi bonobo,
you have to set the 4th parameter in the function to 0.
/Imke
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
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
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
Hi Terry,
please try changing the code in row 10 to this:
End = List.Max({StartDate, EndDate, Date.From(DateTime.LocalNow())})
/Imke
Hi Imke! I replaced the code in line 10 to the code above but it still resulted in error throughout the entire column. Anything else I can do to handle the end date column containing some nulls?
Hi Brian,
sorry for the trouble.
I have uploaded a new function that should cater for empty dates.
Please let me know if that works for you.
Thx, Imke
Imke, thanks as always. But I dont see the code on this page. Is there a link somewhere that I may have missed?
Hi,
try changing your browser. The code is in a GitHub app that might not be sown correctly.
Or you can directly use this link: https://gist.githubusercontent.com/ImkeF/ecb5c0fd6ef791ae4beffd11e6d3f788/raw/da6b67a311f800925bfdee90d8186ce1cc6b2004/Date.Networkdays.pq
/Imke
This. Is. So. Awesome. Thank you. Is there any way to handle null dates in either the start or end dates?
This is amazing. Is there a way to use this function to calculate the end date? Start date + 3 network days = end date?
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
WorkDays.Intl or Workdays
Thank you for this function. Just wanted to check how can we apply this for two custom columns.
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?
Hi Patrick,) returns 2 for me in Excel as well as in Power Query.
I don’t understand what you’re describing.
NETWORKDAYS(15-02-2021, 17-02-2021,
Please see this file: https://www.thebiccountant.com/download/9162/
/Imke
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!
This is perfect and exactly what I was looking for! Thank you so much for making this and for sharing it! 🙂
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)
Where in the code did you insert this?
Hi Karissa,
please check out the first minute of this video where I show how to create a function in Power Query from copied code:
https://youtu.be/2f6vU3Bgi1g
/Imke
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,
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.
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!
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
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))
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))
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
Hello Dave,
you can find such a function here: https://www.reddit.com/r/excel/comments/igah1e/power_query_create_a_workday_function_excluding/
Cheers, Imke
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.
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
hi
i want the end date to always be todays date. when i use the function it only seem to let me choose a fixed date.
i tried to change line 10 to
End = List.Max({StartDate, EndDate, Date.From(DateTime.LocalNow())}),
but i can’t see that option on the dropdown menu for end date.
br
Bjørn J.
Thank your for the networkdays function very nice. I searched online and found a number of options, but yours worked the best and was easy to follow. By chance, does the function allow for a countdown of days from the start date?
Thanks
Kerry Dee
Hi, thanks for this function, unfortunately I am getting error messages when I use it to add a new column to a table. The date format of the columns I am referencing for Start and End date are mm/dd/yyyy and the error message is;
Expression.Error: 1 arguments were passed to a function which expects 3.
Details:
Pattern=
Arguments=[List]
Hi Michelle,
as my function only has 2 mandatory parameters, is there any chance that you’ve modified the code?
I would suggest that you download the code again and make sure to use it as it is.
Thx, Imke
Hi Imke, I downloaded the code again and it is working perfectly. Thanks!
HI Imke. Thanks for this. Sadly I am getting an error on this line:
Number.From(End – Start)
Where its not recognising the calculation and expecting a comma?
Lol – I got the same and was baffled. Then I realised – copy managed to copy an em-dash not an en-dash (or vice-versa) – simply overtype the – and your problem will be solved – or at least mine was!
Pingback: Excel WORKDAY equivalent in Power Query and Power BI –
Thanks for sharing! Nonetheless, I get a error “We cannot convert the value to type Text” when invoking the function ?
Value = 1/01/1900
Type=[Type]
Best regards,
I have the same issue
I got it to work… I cant explain how but I did….
Pingback: Eigene Funktionen in Power Query erstellen - pro-bi.de
Pingback: Convert a Monthly Forecast to Weeks - Brandon A. West
Pingback: Working Days between Dates in Power Query - BI Gorilla
I would like to know if there is any chance wither this code OR if there is other code out there to add workdays (minus weekends / holidays) to a date? I feel like a bit of work on this code would do this but I am not advanced enough to do it. Also I cannot use the JUL 2022 and later versions that might have these natively in PBI DT because of corporate restrictions. Please help!!!
Eric
Hi Eric,
please check this post – should to just that, if my understanding of your request is correct: https://www.thebiccountant.com/2022/02/21/excel-workday-equivalent-in-power-query-and-power-bi/
This is awesome! Worked perfectly–Thank you!
Hi Imke,
I’ve developed a solution which allows you to assign to every row a different list of non working days. You can see it here: https://www.ms-office-forum.net/forum/showthread.php?t=357276&page=3 Post#36
Hi Imke,
Just like everyone else, absolutely love this. I am trying to use this function within a dataflow and some of the functionality seems slightly different – it gives me no option to select the holiday column. How do I need to amend this to use in my flow?
Hi Simon,
yes, unfortunately you need to type this in by hand.
The syntax is: TableName[ColumnName].
Thank you very much!
Great, thanks. Do you know how to exclude the first day from the count?
How could I add some special days which is in weekend but actually it is the working day by some reasons?
I would like to add back these special weekend day as working day to calculate again……
Thank you
Pingback: Date.Networkdays.Intl in Power Query Language M - Quant Insights Network
Hi imke thanks for the query, i have a problem when date column two of them null and the result is 1, it should not be calculated, i want it still counted as null, can you give me an update how should i do