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