Can we use two ranges in Networkday Function?

**e4excel** · 11-08-2008, 11:08 AM

Dear Forum,

I have used the Networkday Function to get the Working Days in a month excluding the Saturdays and Sundays and also the Holidays as Networkday Function by default excludes the Sat/Sun and any of Regional Holidays.

This works absolutely fine, howevr in the realistic scenarion we need to also add any leaves allotted to an employee.

Now if this List of Holidays is entered in a seperate column, how can this be consolidated to get the actual
Working Days = Total Days in a Month - ( Sat/Sun + Regional Holidays) - Leaves )

In the below syntax, the range $I$2:$I$4 contains the Regional Holidays and can be defined as a Name= Holidays

NETWORKDAYS(B2,C2,$I$2:$I$4)

NETWORKDAYS(Startdate,EndDate,Holidays)

Can we incorporate something like a double range like this : Leaves without writing it below the Holiday Range, this new Range by using Offset function can be made into a Dynamic Range..

< =NETWORKDAYS(Startdate,EndDate,Holidays & Leaves)>

Any ideas..!

**Domenic** · 11-08-2008, 01:53 PM

Try...

=NETWORKDAYS(StartDate,EndDate,Holidays)-SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Leaves,0)))

Hope this helps!

**daddylonglegs** · 11-08-2008, 04:38 PM

Another option might be

=NETWORKDAYS(B2,C2,holidays)+NETWORKDAYS(B2,C2,leaves)-NETWORKDAYS(B2,C2)

**e4excel** · 11-09-2008, 07:44 AM

Daddylonglegs and Domenic
You both were simply great and it works really well with both the formulas..

Domenic: I would appreciate if you could explain your formula as I am not a pro like you guys, this can help me in using the same logic and also in not repeating my questions...

One more thing just came to my mind, in Networkdays Function by default it considers Sat/Sun as Holidays , what if it was a Six-day weekend? like only Sunday is a Holiday? Is there a solution to that..I thought of asking as its relevant to the main query..

Thank you folks once again.
God Bless you and this wonderful forum..

**Domenic** · 11-09-2008, 11:53 AM

Domenic: I would appreciate if you could explain your formula as I am not a pro like you guys, this can help me in using the same logic and also in not repeating my questions...

Let's assume that A2 contains the start date of November 1, 2008, B2 contains the end date of November 15, 2008, and that G2:G10 contains the "leaves" of which November 3rd and November 5th are included for the month of November. Here's how the SUMPRODUCT part of the formula is evaluated...

SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(A2&":"&B2)),G2:G10,0)))

SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(39753&":"&39767)),{39755;39757,...},0)))

(Note that dates are stored as serial numbers in Excel. For detailed information on dates, have a look at Excel's help file.)

SUMPRODUCT(--ISNUMBER(MATCH({39753;39754;39755;39756;39757;...;39767},{39755;39757;...},0)))

(Note that INDIRECT returns an array of references specified by text 39753&":"&39767, which is passed to the ROW function. The ROW function, in turn, returns an array of row numbers corresponding to each reference supplied by INDIRECT.)

SUMPRODUCT(--ISNUMBER({#N/A;#N/A;1;#N/A;2;...;#N/A}))

SUMPRODUCT(--{FALSE;FALSE;TRUE;FALSE;TRUE;...;FALSE})

SUMPRODUCT({0;0;1;0;1;...;0})

(Note that the double negative '--' coerces TRUE and FALSE into their numerical equivalent of 1 and 0, respectively.)

Then, SUMPRODUCT simply sums and returns 2.

One more thing just came to my mind, in Networkdays Function by default it considers Sat/Sun as Holidays , what if it was a Six-day weekend? like only Sunday is a Holiday?

Try...

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(StartDate&":"&EndDate)),2)<7),--ISNA(MATCH(ROW(INDIRECT(StartDate&":"&EndDate)),Holidays,0)))

Hope this helps!

**e4excel** · 11-09-2008, 01:21 PM

Dear Dominic

Thank you so much for the explanation as well as the Alternative on ignoring Saturday....

I think you are really very modest in saying ...

HTML Code:

Ofcourse everything helped me..

Thank you so much...