Number of day

[Tom.Jones]

Howdy,

Need to find number of working day between 2 date, for every quarter.
In A2 start date
In B2 end date
In C2 Q1 quarter 1
In D2 Q2 quarter 2
In E2 Q3 quarter 3
In F2 Q4 quarter 4

Start date | end date | Q1 | Q2 | Q3 | Q4
Need how many working day are in Q1 - Q4 for date between start date and end date.

 

[Jeffrey Mahoney]

Please try this

Book1
	C	D	E	F	G	H
2	Start	End	Q1	Q2	Q3	Q4
3	1/15/2023	4/15/2023	76	15	0	0
4	4/2/2023	6/1/2023	0	60	0	0
5	4/2/2023	10/18/2023	0	89	90	18
6	4/2/2023	6/1/2023	0	60	0	0
Sheet1

Cell Formulas
Range	Formula
E3	E3	=MAX(DAYS360(MAX($C3,DATE(YEAR($C3),1,1)),MIN(EOMONTH(DATE(YEAR($C3),3,1),0)+1,$D3+1)))
F3:F6	F3	=MAX(DAYS360(MAX($C3,DATE(YEAR($C3),4,1)),MIN(EOMONTH(DATE(YEAR($C3),6,1),0)+1,$D3+1)),0)
G3:G6	G3	=MAX(DAYS360(MAX($C3,DATE(YEAR($C3),7,1)),MIN(EOMONTH(DATE(YEAR($C3),9,1),0)+1,$D3+1)),0)
H3:H6	H3	=MAX(DAYS360(MAX($C3,DATE(YEAR($C3),10,1)),MIN(EOMONTH(DATE(YEAR($C3),12,1),0)+1,$D3+1)),0)
E4:E6	E4	=MAX(DAYS360(MAX($C4,DATE(YEAR($C4),1,1)),MIN(EOMONTH(DATE(YEAR($C4),3,1),0)+1,$D4+1)),0)

 

[Tom.Jones]

Thank you for replay but I need working days.

 

[Tetra201]

See if the following formula works for you:

=MAX(0,NETWORKDAYS(MAX($A2,DATE(YEAR($A2),RIGHT(C$1)*3-2,1)),MIN($B2,DATE(YEAR($A2),RIGHT(C$1)*3+1,0)),Holidays))

where Holidays is a named range containing holiday dates.

 

[Tom.Jones]

Thank you so much Tetra201.
Excelent job.

 

[Tetra201]

Glad to have helped.