• 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

B

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.
 
Is it only those Saturdays that get excluded? No Sundays or other holidays?
 
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)
 
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
 
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)
 
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 said:
Dear Haseeb, can you just help me to indicate whar A:A represents.. is it calender or holiday list..
Click to expand...

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

  • NETWORKDAYS.zip
    30 KB · Views: 58
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

  • TEST.xlsx
    8.6 KB · Views: 11
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

  • SampleData (1).xlsx
    9.4 KB · Views: 27
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

  • SampleData-check.xlsx
    9.5 KB · Views: 22
You must log in or register to reply here.
Back
Top