excel formula networking days between dates

[President]

Hi,

i need help from you experts in the following formula.

i know how to calculate the working days between two dates with networkingdays.int

however the problem is that i have a specific dates between two months. i want to calculate the working days of James on the condition if it is within the calendar dates betwen column C start date and Column D end date.

i need revised formula in column F5 to F9 to tell me the networking days of James that for each month on the given data range

thanks,.


Excel 2010

	A	B	C	D	E	F	G	H	I	J	K
1	Employee	Monthly Pay	Start Date	End Date							holidays
2	James	2500	9/20/2014	11/11/2014		37					1/1/2014
3											2/15/2014
4		Calendar monthsMonth	Start	End	# of Days in Month	Number of Days That employee was Here this month excluding weekends and holidays	% of Month	Pay for Month
5		Oct	9/20/2014	10/17/2014	28	28	100%	2500.00
6		Nov	10/18/2014	11/14/2014	28	25	89%	2232.14
7		Jan	12/20/2013	1/16/2014	28	0	0%	0.00
8		Feb	1/17/2014	2/13/2014	28	0	0%	0.00
9		Mar	3/21/2014	4/17/2014	28	0	0%	0.00
10		Total						4732.14
11

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F2 =NETWORKDAYS.INTL(C2,D2,1,K2:K3) E5 =D5-C5+1 F5 =MAX(0,MIN(D5,$D$2)-MAX(C5,$C$2)+1) G5 =F5/E5 H5 =+G5*$B$2 E6 =D6-C6+1 F6 =MAX(0,MIN(D6,$D$2)-MAX(C6,$C$2)+1) G6 =F6/E6 H6 =+G6*$B$2 E7 =D7-C7+1 F7 =MAX(0,MIN(D7,$D$2)-MAX(C7,$C$2)+1) G7 =F7/E7 H7 =+G7*$B$2 E8 =D8-C8+1 F8 =MAX(0,MIN(D8,$D$2)-MAX(C8,$C$2)+1) G8 =F8/E8 H8 =+G8*$B$2 E9 =D9-C9+1 F9 =MAX(0,MIN(D9,$D$2)-MAX(C9,$C$2)+1) G9 =F9/E9 H9 =+G9*$B$2 H10 =SUM(H5:H9) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Pup Denab]

HI
=NETWORKDAYS(D8,EOMONTH(D8,0))
This will find the last day of the month, then the networkdays, will work out the work days

 

[President]

HI
=NETWORKDAYS(D8,EOMONTH(D8,0))
This will find the last day of the month, then the networkdays, will work out the work days

i do not believe you understood my question.
the EOmonth will get the end of the month, but the problem is the customised months end of the month is not the same default calendar end of the month.
your formula does not work.

 

[Pup Denab]

ok how about this
=IF(NETWORKDAYS(IF(D8<=$D$3,$D$3,D8),IF(E8<=$E$3,E8,$E$3))<1,0,NETWORKDAYS(IF(D8<=$D$3,$D$3,D8),IF(E8<=$E$3,E8,$E$3)))
This will work out the network days , between the dates in start / end date, in the given example

 

[President]

thanks Pup. it works.

ok how about this
=IF(NETWORKDAYS(IF(D8<=$D$3,$D$3,D8),IF(E8<=$E$3,E8,$E$3))<1,0,NETWORKDAYS(IF(D8<=$D$3,$D$3,D8),IF(E8<=$E$3,E8,$E$3)))
This will work out the network days , between the dates in start / end date, in the given example

 

[jamilm]

shorted version of it

=MAX(0,NETWORKDAYS.INTL(MAX(C6,$C$2),MIN(D6,$D$2),1,$L$2:$L$3))
or =MAX(0,SUM(INT((WEEKDAY(MAX($C6,$C$2)-{2,3,4,5,6})+MIN($D6,$D$2)-MAX($C6,$C$2))/7)))
or
=IF(OR($D$2<$C$2,$C$2>$D6),0,ABS(NETWORKDAYS(MIN($D$2,$D6),MAX($C$2,$C6),$L$2:$L$3)))

 

[President]

Thank you very much jamilm

shorted version of it

=MAX(0,NETWORKDAYS.INTL(MAX(C6,$C$2),MIN(D6,$D$2),1,$L$2:$L$3))
or =MAX(0,SUM(INT((WEEKDAY(MAX($C6,$C$2)-{2,3,4,5,6})+MIN($D6,$D$2)-MAX($C6,$C$2))/7)))
or
=IF(OR($D$2<$C$2,$C$2>$D6),0,ABS(NETWORKDAYS(MIN($D$2,$D6),MAX($C$2,$C6),$L$2:$L$3)))