counting working days in specific months between two dates

[RUDYTX]

Hello!

I need to calculate the number of working days (no Saturdays and Sundays) between two given dates, but i would like to exclude the working days from June, July, August, and December.


A1 is the start range 10/1/13
B1 is the end range 4/3/2015

Thanks for your help!!

 

[timfritsch]

Try =NETWORKDAYS(start_date,end_date)

 

[abmati]

This you can do manually as you want to ignore the month June, July, August and December of each year.

Excel 2007
	A	B	C	D	E
1	10-01-2013	04-03-2015	10-01-2013	Thursday	1
2			11-01-2013	Friday	1
3	Net work days		12-01-2013	Saturday	0
4	385		13-01-2013	Sunday	0
5			14-01-2013	Monday	1
Sheet2

Cell Formulas
Range	Formula
C1	=A1
C2	=IF(C1=0,0,IF(C1=$B$1,0,C1+1))
C3	=IF(C2=0,0,IF(C2=$B$1,0,C2+1))
C4	=IF(C3=0,0,IF(C3=$B$1,0,C3+1))
C5	=IF(C4=0,0,IF(C4=$B$1,0,C4+1))
D1	=IF(C1=0,0,TEXT(C1,"DDDD"))
D2	=IF(C2=0,0,TEXT(C2,"DDDD"))
D3	=IF(C3=0,0,TEXT(C3,"DDDD"))
D4	=IF(C4=0,0,TEXT(C4,"DDDD"))
D5	=IF(C5=0,0,TEXT(C5,"DDDD"))
E1	=IF(C1=0,0,IF(MONTH(C1)=6,0,IF(MONTH(C1)=7,0,IF(MONTH(C1)=8,0,IF(MONTH(C1)=12,0,IF(D1="Saturday",0,IF(D1="Sunday",0,1)))))))
E2	=IF(C2=0,0,IF(MONTH(C2)=6,0,IF(MONTH(C2)=7,0,IF(MONTH(C2)=8,0,IF(MONTH(C2)=12,0,IF(D2="Saturday",0,IF(D2="Sunday",0,1)))))))
E3	=IF(C3=0,0,IF(MONTH(C3)=6,0,IF(MONTH(C3)=7,0,IF(MONTH(C3)=8,0,IF(MONTH(C3)=12,0,IF(D3="Saturday",0,IF(D3="Sunday",0,1)))))))
E4	=IF(C4=0,0,IF(MONTH(C4)=6,0,IF(MONTH(C4)=7,0,IF(MONTH(C4)=8,0,IF(MONTH(C4)=12,0,IF(D4="Saturday",0,IF(D4="Sunday",0,1)))))))
E5	=IF(C5=0,0,IF(MONTH(C5)=6,0,IF(MONTH(C5)=7,0,IF(MONTH(C5)=8,0,IF(MONTH(C5)=12,0,IF(D5="Saturday",0,IF(D5="Sunday",0,1)))))))
A4	=SUM(E1:E1000)

Regards

 

[RUDYTX]

I think this includes all working days. I don't need June, July, August, and December.

 

[RUDYTX]

I will try this Abmati.

Thanks!

 

[barry houdini]

One way would be to use a regular NETWORKDAYS function, but list all June, July, August and December dates within the required period and use that range as the holiday range in NETWORKDAYS, so all those months would be excluded.

alternatively you can use this formula without needing to list any dates

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6)*ISNA(MATCH(MONTH(ROW(INDIRECT(A1&":"&B1))),{6,7,8,12},0)))

where start date is in A1 and end date in B1

 

[RUDYTX]

Thanks a lot. It worked perfectl MVP