NETWORKDAYS.INTL in Power Query M Language

Excel provides two very useful functions -?NETWORKDAYS?and?NETWORKDAYS.INTL?which are not found in Power Query language M.

The key difference between these functions is that in NETWORKDAYS, weekends are fixed to Saturday and Sunday whereas in NETWORKDAYS.INTL, you can control which days should be treated as weekends. For example, many countries have one day weekend say Sunday. Then these countries can't use NETWORKDAYS, they will have to use NETWORKDAYS.INTL. In Gulf countries, Friday and Saturday are treated as weekends making NETWORKDAYS worthless. They will have to use NETWORKDAYS.INTL. In one industrial town, Thursday is the day when power cut is imposed, hence they follow Thursday as weekend (6 days a week working). Hence, here also, NETWORDAYS can't be used. Many countries are advocating 3 days weekends making NETWORKDAYS completely worthless for them.

DAX introduced NETWORKDAYS function in Jul-22 but Power Query M Language hasn't got Date.Networkdays yet. While there are formulas published for NETWORKDYS in Power Query on Web but I can't find anything for NETWORKDAYS.INTL. The scope of this article is limited to NETWORKDAYS.INTL only. However, DAX's NETWORKDAYS provides flexibility to control only either one day or two days as weekends. It can't control weekends other than the weekend pair provided by DAX. Point 2 below is followed by DAX NETWORKDAYS but not Point 1.

Following is the syntax for NETWORKDAYS.INTL

NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

StartDate and EndDate are mandatory parameters. Weekend list and Holidays list are optional parameters.

There are two ways to control weekend parameter

1. Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. Using 1111111 will always return 0.

For example, 0000011 would result in a weekend that is Saturday and Sunday.

2. You can pass numbers also as weekend control parameter as below

Use below function code for Date.Networkdays.Intl function in Power Query. Weekend can be passed as "1" to "7" Or "11" to "17" or as 7 digits string such as "0000110" (This has been described above).?

Holidays can be passed as a list of dates. Hence, note, Holidays is a list not a string.?You can see the examples given in the attached Excel at the end.

Below is the result for some test cases.

Below is the M code for NETWORKDAYS.INTL. This is also published on Github

Excel file containing the code and example can be downloaded from Networkdays.Intl

// NETWORKDAYS.INTL - Authored by Vijay A. Verma
(StartDate, EndDate, optional Weekend, optional Holidays)=>?
let
? ? //if a date is blank, make it equal to 30-Dec-1899
? ? FromDate = if StartDate = null then #date(1899,12,30) else StartDate,
? ? ToDate = if EndDate = null then #date(1899,12,30) else EndDate,
? ? //StartDate should be lesser than or equal to EndDate. If it is more than EndDate, then we will multiply the final result with negative sign. Let's store this sign
? ? Sign = if ToDate<FromDate then -1 else 1,
? ? Start_Date = List.Min({FromDate, ToDate}),
? ? End_Date = List.Max({FromDate, ToDate}),
? ? //Prepare a list of dates from Start date to End date and remove Holidays from there
? ? // Also set Holidays list to blank list if Holidays is null or blank
? ? ListOfAllDates = List.Difference(List.Dates(Start_Date,Duration.Days(End_Date-Start_Date)+1,#duration(1,0,0,0)),if Holidays=null or Holidays="" then {} else Holidays),
? ? //Adjust Weekend Parameter
? ? SetWeekend = if Weekend ="" or Weekend = null then "1" else Text.From(Weekend),
? ? //1 to 7 and 11 to 17 are parameters for weekends - https://support.microsoft.com/en-us/office/networkdays-intl-function-a9b26239-4f20-46a1-9ab8-4e925bfd5e28
? ? WeekendParamList = {"1".."7"} & {"11","12","13","14","15","16","17"},
? ? WeekendLookupList = {"0000011","1000001","1100000","0110000","0011000","0001100","0000110","0000001","1000000","0100000","0010000","0001000","0000100","0000010"},
? ? WeekendString = if Text.Length(SetWeekend)=7 then SetWeekend else WeekendLookupList{List.PositionOf(WeekendParamList,SetWeekend)},
? ? //Let's generate a list which has the position of weekends. Hence for 1000011 will generate, {1,6,7}.
? ? WeekendList = List.RemoveMatchingItems(List.Transform(List.Positions(Text.ToList(WeekendString)), (i)=>(i+1)*Number.From(Text.ToList(WeekendString){i})),{0}),
? ? //Let's remove the dates which match the weekend criteria and take the count multiplied by Sign
? ? Networkdays = Sign * List.Count(List.Select(ListOfAllDates,(i)=>not List.Contains(WeekendList,Date.DayOfWeek(i,1)+1)))
in
? ? Networkdays        

--- End of Article ---