Working days during affected months

[Tigerexcel]

Need a formula that counts the number of days that someone's leave will impact. Any approach is fine, I did ask a similar question previously but have changed my formulae for this example.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Name	Leave Type	Quantity - Days	Start Date	End Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	Jack	Annual Leave	7	23/09/2020	1/10/2020									6	1
3	Mary	Annual Leave	22	4/01/2020	4/02/2020
4	Peter	Annual Leave	8	4/01/2020	15/01/2020
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=NETWORKDAYS(D2,E2)

Row 2 shows the desired output in column N & O.

 

[Misca]

Something like this should work as long as the Start Date and End Date share the same year:
=IF(OR(COLUMNS($F$2:F2)<MONTH($D2),COLUMNS($F$2:F2)>MONTH($E2)),"",NETWORKDAYS(MAX(DATE(YEAR($D2),COLUMNS($F$2:F2),1),$D2),MIN(EOMONTH(DATE(YEAR($D2),COLUMNS($F$2:F2),1),0),$E2)))

The formula compares the number of columns to the month number and when they overlap it calculates the number of working days from MAX(Start of Month, Start Date) to the MIN(End of Month, End Date).

If the month names were actual dates (for example the first of the month) the whole formula could be a lot easier to write (you could compare one date to another) and it would work even if the leave started at the end of one year and ended the next year.

 

[Tigerexcel]

Thank you Misca, perfect! I can't quite follow your last sentence, could you provide an example, it is likely that I will have to work out leave amounts over different years.

 

[Dave Patton]

F2:Q2 are dates like 01-01-20 formatted as Month

T202009c.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	Name	Leave Type	Quantity - Days	Start Date	End Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct
3	Jack	Annual Leave	7	23-Sep-20	1-Oct-20									6	1
4	Mary	Annual Leave	22	4-Jan-20	4-Feb-20	20	2
5	Peter	Annual Leave	8	4-Jan-20	15-Jan-20	8
1f

Cell Formulas
Range	Formula
F3:O5	F3	=IF(MONTH(F$2)<MONTH($D3),"",MAX(0,NETWORKDAYS(MAX(F$2,$D3),MIN(EOMONTH(F$2,0),$E3))))
C3:C5	C3	=NETWORKDAYS(D3,E3)

 

[Tigerexcel]

Thanks Dave, got it now.

 

[Dave Patton]

Thanks for the feedback.