# how to calculate networkdays excluding 2nd & 4th saturday of month

#### Bhushan Joshi

##### New Member
how to calculate networkdays excluding 2nd & 4th saturday of month??
I have tried to calculating..
by adding Week "N2=IFERROR(INT((M2-DATE(YEAR(M2),1,1))/7)+1,"")".. post that check
is it even"O2=+ISEVEN(N2)" and then Weekday by"P2=TEXT(M2,"dddd")"..
TAT In days"Q2=IFERROR(+IF(O2&P2="TRUESaturday",+NETWORKDAYS(D2,M2,Master!\$G\$2:\$G\$56),+NETWORKDAYS(D2,M2,Master!\$G\$2:\$G\$56)-1),"WIP")"

This formula is not working properly as this will fail when there mentioned example:
Eg. If December has 4 Weeks in month & in January there is 5 Weeks in month then 1st week's saturday of February will be non working by this. However this supposed to be Working day.. Need help for concreat solutions.

#### Luke M

##### Excel Ninja
Is it only those Saturdays that get excluded? No Sundays or other holidays?

#### Haseeb A

##### Active Member
Hello Joshi,

Try this:

=NETWORKDAYS(StartDay,EndDay,Holidays)+SUMPRODUCT((EOMONTH(StartDay,ROW(INDEX(A:A,1):INDEX(A:A,DATEDIF(StartDay,EndDay,"m")+1))-2)+1+7*{2,4}-WEEKDAY(EOMONTH(StartDay,ROW(INDEX(A:A,1):INDEX(A:A,DATEDIF(StartDay,EndDay,"m")+1))-2)+1-7)<=EndDay)+0)

#### NARAYANK991

##### Excel Ninja
Hi Joshi ,

Can you please say what should be the expected result for the following two dates , without any Holidays list ?

Start Date : 3/3/2013

End Date : 7/17/2013

Narayan

#### Haseeb A

##### Active Member
Narayan's post brought me an attention, just using <=EndDay will fail, if start date is >2nd or 4th Sat. So I think use of FREQUENCY will overcome this issue.

=NETWORKDAYS(StartDay,EndDay,Holidays)+INDEX(FREQUENCY(EOMONTH(StartDay,ROW(INDEX(A:A,1):INDEX(A:A,DATEDIF(StartDay,EndDay,"m")+1))-2)+1+7*{2,4}-WEEKDAY(EOMONTH(StartDay,ROW(INDEX(A:A,1):INDEX(A:A,DATEDIF(StartDay,EndDay,"m")+1))-2)+1-7),IF({1,0},StartDay,EndDay)),2)

#### Bhushan Joshi

##### New Member
Dear All, thnak you for your valuable time..

Dear Narayan, As per me it should calculate 91 days..

#### Bhushan Joshi

##### New Member
Dear Luke,

It should omit all Sundays along with 2nd & 4th Saturday of Month.. As 2nd & 4th Saturday will be non-working Saturday's.

#### Bhushan Joshi

##### New Member
Dear Haseeb, can you just help me to indicate whar A:A represents.. is it calender or holiday list..

#### Luke M

##### Excel Ninja
Dear Haseeb, can you just help me to indicate whar A:A represents.. is it calender or holiday list..
Answering for Haseeb, it doesn't represent anything. The formula just uses that range to construct a list of numbers/dates. You don't need to change it to anything.

#### NARAYANK991

##### Excel Ninja
Hi Bhushan ,

I do not know how you got 91 for the dates I have mentioned ; I will repeat those dates here :

Start Date : 3/3/2013

End Date : 7/17/2013

There are 137 days in the above period , including both Start Date and End Date ; out of these , there are 20 Sundays , and 9 second Saturdays ( the 2nd and 4th Saturdays in every month are the second Saturdays ).

If you subtract 29 from 137 , you should get 108. Of course , if you exclude either Start Date or End Date or both , there will be a difference of one or two days.

Can you explain how you have arrived at 91 ?

Narayan

#### Sajan

##### Excel Ninja
Hi Haseeb,
When I tried your second formula against the following dates
StartDay EndDay Holidays
15-Jan-13 9-Jun-13 4-Jul-13

I got an output of 113 days, whereas the output should be 114 days

The 2nd and 4th Saturdays that need to be excluded are:
{"Jan 26","Feb 09","Feb 23","Mar 09","Mar 23","Apr 13","Apr 27","May 11","May 25","Jun 08"}

I think by incrementing the output from DATEDIF by 2 instead of 1, the counts get corrected.

Here is one more approach that avoids the DATEDIF function:
=NETWORKDAYS(StartDay,EndDay,Holidays)+SUM((MOD(ROW(INDEX(A:A,StartDay):INDEX(A:A,EndDay)), 7)=0)* (MOD(WEEKNUM(ROW(INDEX(A:A,StartDay):INDEX(A:A,EndDay)))-WEEKNUM(EOMONTH(ROW(INDEX(A:A,StartDay):INDEX(A:A,EndDay)),-1)+1)+1,2)=0))

enter with Ctrl + Shift + Enter

Regards,
Sajan.

#### NARAYANK991

##### Excel Ninja
Hi ,

Attached is a document which explains the formula. The formulae have all been developed by Sajan.

The file is a Word document , but since this forum does not allow this file type to be uploaded , the file has been zipped.

Narayan

#### Attachments

• 30 KB Views: 38

#### lohithsriram

##### Active Member
Narayan,

Excellent explaination on the formula split up. Thank you.

Hi Lohith ,

Thanks.

Narayan

#### srivastav

##### New Member
Hi all,

lil. bit confused with the above formula, i have attached the excel please write the formula in the excel and help me sort this out..

#### Attachments

• 8.6 KB Views: 5

#### NARAYANK991

##### Excel Ninja
Hi ,

3 named ranges have been defined :

StartDate , referring to the cell A2

EndDate , referring to the cell B2

Holidays , referring to the range \$D\$2:\$D\$15

Narayan

#### Attachments

• 9.4 KB Views: 10

#### srivastav

##### New Member
Hi,

In the file which you have provided in the cell c2 you have written the formulae for the same, but it seems like while just calculating with the network days function i am getting the same value.(i.e., with including 2nd and 4th Saturday, please help..

and what also if i have to calculate the same to the number of values (i.e., same to be copied and pasted in the complete column)..

#### Attachments

• 9.5 KB Views: 9

#### NARAYANK991

##### Excel Ninja
Hi ,

Sorry , but the formula is an array formula , and should be entered using CTRL SHIFT ENTER.

Narayan