NETWORKDAYS.INTL holiday question

[ColinPearsonEIT]

Good Morning/Afternoon/Evening, depending on where you are on the planet. I have most of a sheet developed that counts work days, Saturdays worked, Sundays worked, and tallies up hours that are worked at straight time, time and a half, and double time. I had previously ignored holidays since this is for estimating purposes only and if I'm off by a a few days per year, it's not a big deal. However, one of our sales guys asked me if I could modify the sheet to exclude holidays. So I made a list of all the holidays for the next several years and began to mess with it to see how it works.

It looks like putting an argument in the [holiday] section of NETWORKDAYS.INTL works as I expected, except that it removes a day from the result for EVERY holiday in your list, whether or not they fall within the start_date and end_date.

HEre's some examples of the results I get using with the date range of 2024/01/01 - 2024/12/31, assuming we work 7 days/week
=IF(ISBLANK(C52),0,NETWORKDAYS.INTL(C52,D52,K52,0)) --- I get 366 days worked (being a leap year this year)
=IF(ISBLANK(C52),0,NETWORKDAYS.INTL(C52,D52,K52,HOLIDAZE[2024])) --- I get 353 by removing the 13 holidays in 2024 we would normally get off
=IF(ISBLANK(C52),0,NETWORKDAYS.INTL(C52,D52,K52,HOLIDAZE[[2024]:[2030]]))--- I EXPECTED to still get 353 but instead get 314 b/c it's removing all holidays from my list of 2024-2030 holidays that I created.

Is there a way to exclude holidays only if they fall within the date range entered by the user?

THANKS!!

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ColinPearsonEIT]

@Fluff I'm not able to install that add-in, it says :This file type is not supported in Protected View".
To clarify a bit though, here's what the cells in the formula are:
C52 - start date entered by user (I have 1/1/2024)
D52 - end date entered by user (I have 12/31/2024)
K52 - working days code (in this case for 7 days/week, I have "0000000" entered in K52)

Hope that helps. Thank you!

 

[Fluff]

it says :This file type is not supported in Protected View".

Have a look here XL2BB Icons greyed out

 

[Dave Patton]

N.B. I do not know the dates on your Holiday list; I just made up dates for the example.
K2 is Text entered like "0000000" in a cell initially formatted as text.

Work Days.xlsm
	A	B	C	D	E	F	G				K
1							Holidays
2	Work 7 day Week	01-Jan-24	31-Dec-24	366			01-Jan-24				0000000
3	"" less Holidays	01-Jan-24	31-Dec-24	362			01-Jul-24
4	"" less Holidays	01-Jan-24	31-Dec-24	362			01-Aug-24
5	"" less Holidays	01-Jan-24	31-Dec-24	362			01-Jan-25
6							01-Jul-25
7							01-Aug-25
8							01-Sep-24
1a

Cell Formulas
Range	Formula
D2	D2	=C2-B2+1
D3:D4	D3	=NETWORKDAYS.INTL(B3,C3,"0000000",Holidays)
D5	D5	=NETWORKDAYS.INTL(B5,C5,K2,Holidays)

Named Ranges
Name	Refers To	Cells
Holidays	='1a'!$G$2:$G$8	D3:D5

 

[ColinPearsonEIT]

MANHOURS-DAYS WORKED-ST-OT-DT.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
52	XXX	1	1/1/2024	12/31/2024	2x7x13	52.29	12.07	2	7	13	0000000	1111111	314	3360	3452	1352	52	52
Hourly Breakdown

Cell Formulas
Range	Formula
F52	F52	=IFS(ISBLANK(C52),0,D52<C52,"ERROR",D52=C52,1/7,D52>C52,ROUNDUP((D52-C52+1)/7,RoundingPrecisionWEEKS))
G52	G52	=IFS(ISBLANK(C52),0,D52<C52,"ERROR",C52<=D52,ROUND(F52/(52/12),RoundingPrecisionMONTHS))
H52	H52	=IF(ISBLANK(C52),0,INDEX(WORK_SCHEDULES[#All],MATCH(E52,WORK_SCHEDULES[[#All],[SCHEDULES]],0),2))
I52	I52	=IF(ISBLANK(C52),0,INDEX(WORK_SCHEDULES[#All],MATCH(E52,WORK_SCHEDULES[[#All],[SCHEDULES]],0),3))
J52	J52	=IF(ISBLANK(C52),0,INDEX(WORK_SCHEDULES[#All],MATCH(E52,WORK_SCHEDULES[[#All],[SCHEDULES]],0),4))
K52	K52	=IF(ISBLANK(C52),0,INDEX(WORK_SCHEDULES[#All],MATCH(E52,WORK_SCHEDULES[[#All],[SCHEDULES]],0),5))
L52	L52	=IF(ISBLANK(C52),0,INDEX(WORK_SCHEDULES[#All],MATCH(E52,WORK_SCHEDULES[[#All],[SCHEDULES]],0),6))
M52	M52	=IF(ISBLANK(C52),0,NETWORKDAYS.INTL(C52,D52,K52,HOLIDAZE[[2024]:[2030]]))
N52	N52	=IF(ISBLANK(C52),0,B52*((M52-Q52-R52)*8*H52))
O52	O52	=IF(ISBLANK(C52),0,B52*(((M52-Q52-R52)*((J52-8)*H52)+(Q52*J52*H52))))
P52	P52	=IF(ISBLANK(C52),0,B52*(R52*J52*H52))
Q52	Q52	=IF(ISBLANK(C52),0,SUM(INT((WEEKDAY(C52-7)+D52-C52)/7))*LEFT(RIGHT(L52,2),1))
R52	R52	=IF(ISBLANK(C52),0,SUM(INT((WEEKDAY(C52-1)+D52-C52)/7))*RIGHT(L52,1))

Named Ranges
Name	Refers To	Cells
RoundingPrecisionMONTHS	='Schedules & Holidays'!$D$26	G52
RoundingPrecisionWEEKS	='Schedules & Holidays'!$D$25	F52

 

[ColinPearsonEIT]

@Fluff got it, it was the trusted location part, thank you.
@Dave Patton that is basically what I have I think. I see you have holidays outside the date range like I'm wondering about, but it doesn't count those out. I've hidden some columns, hopefully this posts a bit cleaner. Never used the mini-sheet thing so I'm not sure.

MANHOURS-DAYS WORKED-ST-OT-DT.xlsx
	A	B	C	D	E	F	G						M
52	XXX	1	1/1/2024	12/31/2024	2x7x13	52.29	12.07						314
Hourly Breakdown

Cell Formulas
Range	Formula
F52	F52	=IFS(ISBLANK(C52),0,D52<C52,"ERROR",D52=C52,1/7,D52>C52,ROUNDUP((D52-C52+1)/7,RoundingPrecisionWEEKS))
G52	G52	=IFS(ISBLANK(C52),0,D52<C52,"ERROR",C52<=D52,ROUND(F52/(52/12),RoundingPrecisionMONTHS))
M52	M52	=IF(ISBLANK(C52),0,NETWORKDAYS.INTL(C52,D52,K52,HOLIDAZE[[2024]:[2030]]))

Named Ranges
Name	Refers To	Cells
RoundingPrecisionMONTHS	='Schedules & Holidays'!$D$26	G52
RoundingPrecisionWEEKS	='Schedules & Holidays'!$D$25	F52

 

[ColinPearsonEIT]

@Fluff got it, it was the trusted location part, thank you.
@Dave Patton that is basically what I have I think. I see you have holidays outside the date range like I'm wondering about, but it doesn't count those out. I've hidden some columns, hopefully this posts a bit cleaner. Never used the mini-sheet thing so I'm not sure.

MANHOURS-DAYS WORKED-ST-OT-DT.xlsx
	A	B	C	D	E	F	G						M
52	XXX	1	1/1/2024	12/31/2024	2x7x13	52.29	12.07						314
Hourly Breakdown

Cell Formulas
Range	Formula
F52	F52	=IFS(ISBLANK(C52),0,D52<C52,"ERROR",D52=C52,1/7,D52>C52,ROUNDUP((D52-C52+1)/7,RoundingPrecisionWEEKS))
G52	G52	=IFS(ISBLANK(C52),0,D52<C52,"ERROR",C52<=D52,ROUND(F52/(52/12),RoundingPrecisionMONTHS))
M52	M52	=IF(ISBLANK(C52),0,NETWORKDAYS.INTL(C52,D52,K52,HOLIDAZE[[2024]:[2030]]))

Named Ranges
Name	Refers To	Cells
RoundingPrecisionMONTHS	='Schedules & Holidays'!$D$26	G52
RoundingPrecisionWEEKS	='Schedules & Holidays'!$D$25	F52

this is better with headings I bet.

MANHOURS-DAYS WORKED-ST-OT-DT.xlsx
	A	B	C	D	E	F	G						M
9	EQUIPMENT	QTY	START DATE	END DATE	WORK SCHEDULE	# WEEKS	# MONTHS						# WORK DAYS
52	XXX	1	1/1/2024	12/31/2024	2x7x13	52.29	12.07						314
Hourly Breakdown

Cell Formulas
Range	Formula
F52	F52	=IFS(ISBLANK(C52),0,D52<C52,"ERROR",D52=C52,1/7,D52>C52,ROUNDUP((D52-C52+1)/7,RoundingPrecisionWEEKS))
G52	G52	=IFS(ISBLANK(C52),0,D52<C52,"ERROR",C52<=D52,ROUND(F52/(52/12),RoundingPrecisionMONTHS))
M52	M52	=IF(ISBLANK(C52),0,NETWORKDAYS.INTL(C52,D52,K52,HOLIDAZE[[2024]:[2030]]))

Named Ranges
Name	Refers To	Cells
RoundingPrecisionMONTHS	='Schedules & Holidays'!$D$26	G52
RoundingPrecisionWEEKS	='Schedules & Holidays'!$D$25	F52

Cells with Data Validation
Cell	Allow	Criteria
E10:E52	List	='Schedules & Holidays'!$B$2:$B$23

 

[ColinPearsonEIT]

@Fluff @Dave Patton ... I see what the problem is, and it's 100% me LOL... My dates in my table of holidays for the years 2024 - 2030 have all MM/DD changed as needed for the differing dates over the years, but for YYYY I have 2024 for every year... so my range of 1/1/24-12/31/24 includes ALL of my holiday dates simply because I entered them incorrectly.

 

[Fluff]

Glad you sorted it & thanks for letting us know.