In WORKDAY function, how to exclude multiple/variable holiday rang

[Harold Shea]

In the following formula, G21 contains a date, H21 contains the number of
days that the function should add on to that date, and M20:M100 contains a
list of dates to exclude from the calculation:

=WORKDAY(G21,H21,M20:M100)

This works fine. The problem is that this is for a schedule form that will
be used for many different projects, from vendors scattered worldwide. The
vendors all have different holiday schedules. Currently, the list in M shows
ALL of those holidays, but really, they're scattered across our company and
all of the vendors, who are identified in Column L, like in this example:

COLUMN L COLUMN M
Our office Date
Our office Date
Our office Date
Vendor X Date
Vendor X Date
Vendor X Date
Vendor Y Date
Vendor Y Date
Vendor Z Date

What I'd like to do is make the schedule form more selective, so that if a
certain vendor is involved on a project, only their holidays are added to
those of our office (which are constant). I already have a column (Column F)
where the user identifies the vendor. I guess my question is this: can the
formula be rewritten so that it reads the vendor entered in Column F (for
this example, say cell F21), and then calculate the workdays with only the
holidays for our office and that particular vendor (say, Vendor Y) excluded?

It kind of seems like I should be able to embed the holiday argument in the
WORKDAY function with with a VLOOKUP or INDEX function of some sort, but I
can't figure it out--I keep runningup against the issue of trying to coax a
single range for the WORKDAY holdiday exclusion from a noncontiguous range.

The "Our office" holidays always appear at the top of the list and should
always be excluded. It's only the vendor-specific holidays that will be
variable. It guess a way of restating all of this is to say that the WORKDAY
formula ALWAYS needs to exclude the "Our office" holidays in Column M, but
needs to also exclude those of one vendor (identified in Column F) in the
same list.

I hope this makes sense. To further complicate things, I can't resort to
VBA, because some users are on Macs, Office 2007 for mac doesn't support
macros. If anyone can help, I'd really appreciate it.

 

[Luke M]

As you found out, you have to have a contiguous range. However, we can use an
array IF function to change holidays that you don't want to include to 0
(which evaluates to a date of 1/0/1900)

=WORKDAY(G21,H21,--IF((L20:L100=F21)+(L20:L100="Our office"),M20:M100))

Input this as an array formula by using Ctrl+Shift+Enter to confirm formula,
not just Enter. The nice thing about this formula is that it doesn't require
the vendor names to be sorted and/or grouped.

 

[Brad]

Luke has a good solution - another solution is to have an if statment for
your dates. =If(Group="Our Office",date,0)

Where Group is a range name for a specific cell.

This would also work

 

[Harold Shea]

This works WONDERFULLY! Thank you so much for the fast and creative solution!
I honestly do not believe I would ever have thought of this. You've made
today much, much better!

 

[Harold Shea]

Brad: Thanks for the alternative solution. Luke's ended up worked pretty
well, so I'll probably keep this idea on file for another day, but I really
appreciate the help. Thanks again!