Exclude certain dates fron holidays in the NETWORKDAYS function

[Rainmanne]

I've set up a worksheet to calculate holidays. There is a table consisting two columns. On column with dates, which include and days-off (holiday, public holidays and business trips) and another column with types of holiday ("holiday", "Public Holiday" and "business trip"). I use this table for several calculations.

There is another table, which has three columns: start date, end date and number of days, where I calculate holidays. I use a NETWORKDAYS function to exclude public holidays and weekends from calculations if they happen during my holiday. I tried to use the previous table of all days-off for the [holidays] field. But in order to distinguish between holidays and public holidays I tried to use INDEX/MATCH functions to exclude only "public holidays":

=NETWORKDAYS(J14,K14,INDEX($A$2:$A$97,MATCH("Public Holiday",$C$2:$C$97,0)))

However, it does not seem to work. Basically I need the NETWORKDAYS function to take into account only "public holidays". I guess it should be something to do with array. Is it possible to do?

 

[tyija1995]

So you essentially don't want any weekend parameter right?

You can use binary to eliminate weekends from NETWORKDAYS.INTL function

I.e.
=NETWORKDAYS.INTL(TODAY(),TODAY()+9,"0000000")
will return 10 --> you can use the last parameter of the function to include your public holidays - is this what you're after?

 

[Marcelo Branco]

Maybe...

=NETWORKDAYS(J14,K14,INDEX(($C$2:$C$97="Public Holiday")*$A$2:$A$97,))

M.

 

[Rainmanne]

Maybe...

=NETWORKDAYS(J14,K14,INDEX(($C$2:$C$97="Public Holiday")*$A$2:$A$97,))

M.

This seems to work! Thanks a lot!

 

[Rainmanne]

So you essentially don't want any weekend parameter right?

You can use binary to eliminate weekends from NETWORKDAYS.INTL function

I.e.
=NETWORKDAYS.INTL(TODAY(),TODAY()+9,"0000000")
will return 10 --> you can use the last parameter of the function to include your public holidays - is this what you're after?

No, I want to exclude holidays (like my holidays allowance) and leave only weekends and public holidays.

 

[Marcelo Branco]

This seems to work! Thanks a lot!

You are welcome. Thanks for the feedback.

M.