Network days from a dropdown

[ChrisFoster]

This should be easy for one of you guys.
I have a dropdown list in cell A1, with the 1st date of each month (So 01/01/2024, 01/02/2024, 01/03/2024 etc).

What I want is a formula to go into the cell B1 that returns the number of working days in that month (excluding bank holidays).

What formula do I need please?

 

[Asbestos_Jen]

Assuming your working days exclude Saturday and Sunday, you can use this:

Book1
	A	B	C	D
1	1-Jan-24	22		Holidays
2	1st of month	Working days in month		1-Jan-24
3				19-Feb-24
4				29-Mar-24
5				20-May-24
6				21-Jun-24
7				1-Jul-24
8				5-Aug-24
9				2-Sep-24
10				30-Sep-24
11				14-Oct-24
12				11-Nov-24
13				25-Dec-24
14				26-Dec-24
Sheet1

Cell Formulas
Range	Formula
B1	B1	=NETWORKDAYS(A1,EOMONTH(A1,0),Holidays)

 

[ChrisFoster]

Assuming your working days exclude Saturday and Sunday, you can use this:

Book1
	A	B	C	D
1	1-Jan-24	22		Holidays
2	1st of month	Working days in month		1-Jan-24
3				19-Feb-24
4				29-Mar-24
5				20-May-24
6				21-Jun-24
7				1-Jul-24
8				5-Aug-24
9				2-Sep-24
10				30-Sep-24
11				14-Oct-24
12				11-Nov-24
13				25-Dec-24
14				26-Dec-24
Sheet1

Cell Formulas
Range	Formula
B1	B1	=NETWORKDAYS(A1,EOMONTH(A1,0),Holidays)

Thanks, this just brings up result of #NAME?

 

[Kaper]

Select D2:D14 Press Ctrl+F3 and select new - it shall bring you to creation of named range called Hollidays, referring to your_sheet!$D$2:$D$14 . Once you accept it and recalculate (use F9 for recalculation) you shall have proper result

 

[Asbestos_Jen]

Sorry, @ChrisFoster, I should have stated that Holidays was a named range for the dates listed in D$2:$D$14. You can/should use either the named range tools as @Kaper described or a range that contains the list of bank holidays that applies to you.