Networkdays by quarter and holidays

[ac1983]

Hi everyone,

I have calculated the total number of workdays between two dates by quarter using the formula below:

=MAX(0,NETWORKDAYS(MAX($A2,C$1),MIN($B2,EOMONTH(C$1,2))))

Example:

Start Date	End Date	1/1/2019	4/1/2019	7/1/2019	10/1/2019
1/1/2019	4/30/2019	64	22	0	0
1/1/2019	12/31/2019	64	65	66	66

Now, I would like to reduce the numbers for each quarter by the number of holidays in each quarter. Does anyone have any suggestions on how I can incorporate holidays into this formula?

Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
Networkdays has an argument for holidays, have a look here

NETWORKDAYS Function

The Excel NETWORKDAYS function calculates the number of working days between two dates. NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and can optionally exclude a list of holidays supplied as dates.

exceljet.net

 

[ac1983]

Hi & welcome to MrExcel.
Networkdays has an argument for holidays, have a look here

NETWORKDAYS Function

The Excel NETWORKDAYS function calculates the number of working days between two dates. NETWORKDAYS automatically excludes weekends (Saturday and Sunday) and can optionally exclude a list of holidays supplied as dates.

exceljet.net

Yes, I'm aware but not sure how to apply that to the problem above.

 

[Fluff]

Do you have the holidays listed anywhere?

 

[ac1983]

Yes, I have considered two approaches for holidays:

Approach 1 (List of Holidays):

Holidays
1/1/2019
5/27/2019
7/4/2019
9/2/2019
11/28/2019
12/25/2019

Approach 2 (Count of Holidays per Quarter):

Quarter	# of Holidays
Q1	1
Q2	1
Q3	2
Q4	2

 

[Fluff]

In that case you can just add the holiday range (using approach 1) to your formula like

=MAX(0,NETWORKDAYS(MAX($A2,C$1),MIN($B2,EOMONTH(C$1,2)),$H$2:$H$7))

just change the H2:H7 to the range where your dates are.

 

[ac1983]

In that case you can just add the holiday range (using approach 1) to your formula like

=MAX(0,NETWORKDAYS(MAX($A2,C$1),MIN($B2,EOMONTH(C$1,2)),$H$2:$H$7))

just change the H2:H7 to the range where your dates are.

Worked beautifully. Thanks so MUCH!

 

[Fluff]

Glad to help & thanks for the feedback.