NetworkDay

[linkn00]

I am using the script below to calculate the work day between two days in the power query. But it errors out when there is a negative result. For example, the end date is 3/7 but the start date is 3/9 (End days before the start day).
is there a way to allow this to give me a negative value if we have a negative result? Thank you so much

let
CountBusinessDays = (StartDate as date, EndDate as date) =>
let
numDays = Duration.Days(EndDate - StartDate) + 1,
weekends = List.Select(List.Dates(StartDate, numDays, #duration(1,0,0,0)),
each Date.DayOfWeek(_, Day.Monday) >= 5),
businessDays = numDays - List.Count(weekends)
in
businessDays
in
CountBusinessDays

 

[James006]

Hi,
Take a look at following solution :

Great Tip - NETWORKDAYS Custom Function in Power Query

There have been a lot of questions on the forum lately about the best way to calculate number of workdays not including holidays between two dates. Most of the discussion has focused on DAX-based solutions. This is totally doable via DAX, but as you’ve seen from the recent threads not...

forum.enterprisedna.co

 

[JGordon11]

(StartDate as date, EndDate as date) =>
let
    m = if StartDate <= EndDate then 1 else -1,
    numDays = Number.Abs(Duration.Days(EndDate - StartDate) + m),
    weekends = List.Select(List.Dates(StartDate, numDays, m * #duration(1,0,0,0)),
        each Date.DayOfWeek(_, Day.Monday) > 4),
    businessDays = m * (numDays - List.Count(weekends))
in
    businessDays

 

[linkn00]

Thank you so much for all your help. love it

 

[jdellasala]

The post from Enterprise DNA has a great function, but the web site used (2023 Federal Holiday Calendar | National Holidays | Federal Holidays) isn't helpful. It took forever to connect to, doesn't have any way to request a specific year, and provides just 6 tables of holidays with no indication of what year the tables are for. Dates are only given by Month Name and Date!
There is, however, a very useful site that lists days that the market is closed - Market Holidays for United States for 2023. I found it last year and at the time I found some errors in it - specifically it wasn't handling MLK day properly, but it was corrected shortly after I emailed them. Same thing with Juneteenth! Adding a year to the end of the URL - Market Holidays for United States for 1980 - will provide the calendar for that year.
Anyway, I wrote a PQ Function to create a Calendar Table of those dates:

let
    MarketHolidays = ( YearToGet as any ) as table =>
let
    Source = Web.Page(Web.Contents( "https://www.mypivots.com/market-holidays/united-states/" & Text.From( YearToGet ) )),
    HolCal = Source{0}[Data],
    ChangedType = Table.TransformColumnTypes( HolCal,{{"Date", type date}, {"Holiday", type text}}),
    SortedRows = Table.Sort(ChangedType,{{"Date", Order.Ascending}})
in
    SortedRows
in
    MarketHolidays

The Row Sort is because after adding Juneteenth it's listed after MLK day instead of after Memorial Day!
What I love about that function is making a table of Market Holidays for multiple years is a breeze.

let
    Source = {2000..2025},
    ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    InvokedfxMarketHolidays = Table.AddColumn(ConvertedToTable, "fxMarketHolidays", each fxMarketHolidays([Column1])),
    ExpandedfxMarketHolidays = Table.ExpandTableColumn(InvokedfxMarketHolidays, "fxMarketHolidays", {"Date", "Holiday"}, {"Date", "Holiday"}),
    ChangedType = Table.TransformColumnTypes(ExpandedfxMarketHolidays,{{"Date", type date}, {"Holiday", type text}}),
    RemovedOtherColumns = Table.SelectColumns(ChangedType,{"Date", "Holiday"})
in
    RemovedOtherColumns

Which produces a calendar of Market Holidays from 2000 to 2025.
Hope someone finds it useful!

 

[jdellasala]

(StartDate as date, EndDate as date) =>
let
    m = if StartDate <= EndDate then 1 else -1,
    numDays = Number.Abs(Duration.Days(EndDate - StartDate) + m),
    weekends = List.Select(List.Dates(StartDate, numDays, m * #duration(1,0,0,0)),
        each Date.DayOfWeek(_, Day.Monday) > 4),
    businessDays = m * (numDays - List.Count(weekends))
in
    businessDays

As usual, great tight code.
I've tried to figure out how to make it like the NETWORKDAYS.INTL function - allow either a number or a 7 character string of 1's and 0's or either to indicate work and non-work days, and to remove holidays in a list, but it is way beyond my M Code skills.
Care to take a stab at it?

 

[JGordon11]

M function named NetWorkDays:

(StartDate as any, EndDate as any, optional WeekEnd as any, optional Holidays as nullable list) as number =>
let
    startdate = Date.From(StartDate),
    enddate = Date.From(EndDate),
    m = if startdate <= enddate then 1 else -1,
    numDays = Number.Abs(Duration.Days(enddate - startdate) + m),
    weekend =  if Value.Is(WeekEnd, Number.Type) 
                then 
                    let tbl = Table.FromRows({
                        {1,"Saturday, Sunday","0000011"},{2,"Sunday, Monday","1000001"},{3,"Monday, Tuesday","1100000"},
                        {4,"Tuesday, Wednesday","0110000"},{5,"Wednesday, Thursday","0011000"},{6,"Thursday, Friday","0001100"},
                        {7,"Friday, Saturday","0000110"},{11,"Sunday only","0000001"},{12,"Monday only","1000000"},{13,"Tuesday only","0100000"},
                        {14,"Wednesday only","0010000"},{15,"Thursday only","0001000"},{16,"Friday only","0000100"},{17,"Saturday only","0000010"}})
                    in   tbl{[Column1 = WeekEnd]}[Column3]
                else
                    if WeekEnd = null then "0000011" else WeekEnd,
    dates = List.Dates(startdate, numDays, m * #duration(1,0,0,0)),
    weekendDayCount = List.Sum(List.Transform(dates, each 
        let n = Date.DayOfWeek(_, Day.Monday) in if Text.Middle(weekend, n,1) = "1" then 1 else 0)),
    holidayCount = if Holidays = null then 0 else List.Sum(List.Transform(List.Distinct(Holidays), each let dt = Date.From(_) in 
        if m*Duration.Days(dt - startdate)>=0 and m*Duration.Days(enddate - dt) >=0 and Text.Middle(weekend, Date.DayOfWeek(dt, Day.Monday),1) = "0" then 1 else 0)),
    businessDays = m * (numDays - weekendDayCount - holidayCount)
in
    businessDays

Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    HolidayList = Excel.CurrentWorkbook(){[Name="Holidays"]}[Content][Holidays],
    tbl = Table.AddColumn(Source, "NetWorkDays", each NetWorkDays([Start],[End],[Weekend Type], HolidayList)),
    Result = Table.RemoveColumns(tbl,{"Start", "End", "Weekend Type"})
in
    Result

pq networkdays.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	=TODAY()+RANDBETWEEN(-100,100)	=TODAY()+RANDBETWEEN(1,100)			=TODAY()+RANDBETWEEN(-100,100)
2	Table1				Holidays					Query Output
3	Start	End	Weekend Type		Holidays	DOW		NETWORKDAYS.INTL		NetWorkDays		Test Result
4	4/10/2023	6/8/2023	0101010		5/1/2023	0		30		30		TRUE
5	2/7/2023	5/29/2023	1100000		4/9/2023	6		75		75		TRUE
6	2/21/2023	5/16/2023	11		4/6/2023	3		66		66		TRUE
7	4/15/2023	5/17/2023	1001001		4/15/2023	5		16		16		TRUE
8	1/27/2023	1/9/2023	1010101		1/12/2023	3		-7		-7		TRUE
9	1/8/2023	3/2/2023	5		12/31/2022	5		34		34		TRUE
10	3/23/2023	1/10/2023	0010110		1/12/2023	3		-37		-37		TRUE
11	6/10/2023	4/20/2023	0100000		3/21/2023	1		-41		-41		TRUE
12	12/15/2022	1/8/2023	13		2/14/2023	1		18		18		TRUE
13	3/21/2023	5/27/2023	1111011		12/15/2022	3		9		9		TRUE
14	3/2/2023	4/8/2023	1101111		4/28/2023	4		5		5		TRUE
15	5/17/2023	4/25/2023	0001100		12/11/2022	6		-15		-15		TRUE
16	5/7/2023	12/29/2022	7		12/30/2022	4		-84		-84		TRUE
17	6/5/2023	6/18/2023	1110010		6/11/2023	6		5		5		TRUE
18	5/16/2023	3/16/2023	12		2/28/2023	1		-47		-47		TRUE
19	3/12/2023	4/24/2023	1000101		6/19/2023	0		21		21		TRUE
20	2/22/2023	3/25/2023	1		2/20/2023	0		21		21		TRUE
21	4/29/2023	4/22/2023	1001000		5/31/2023	2		-5		-5		TRUE
22	12/26/2022	3/30/2023	5		1/20/2023	4		60		60		TRUE
23	5/5/2023	3/8/2023	17		12/16/2022	4		-46		-46		TRUE
24	6/20/2023	3/27/2023	1111100		5/10/2023	2		-21		-21		TRUE
25	2/28/2023	12/29/2022	11					-46		-46		TRUE
26	5/9/2023	12/6/2022	2					-99		-99		TRUE
27
Sheet2

Cell Formulas
Range	Formula
F4:F24	F4	=WEEKDAY([@Holidays],3)
H4:H26	H4	=NETWORKDAYS.INTL(A4,B4,C4,Holidays[Holidays])
L4:L26	L4	=H4=J4