• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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.
 

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 Luke,

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

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.
 

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

NARAYANK991

Excel Ninja
Hi ,

See your file now.

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

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)..

please advice
 

Attachments

Top