I have a client that pays me in 30 business / working day. I have an
excel file that I have laid out in the following manner:

A D E
G H --> DW
Expected
Customer Invoice Inv Pay
Name Date Amt Date Dates
starting with 12/31/10 thru 4/29-11

Day of the week (calculated) This
line tells me if the above date is a SUN,

MON, TUE, --> SAT
Is this a holiday? (Manually changed) This line
has a YES or NO
Count as a holiday (calculated) If
this day is a MON-FRI and is a YES for

"Is this a holiday, it has a value of 1,

or if false, NO.

Then I list the client below all of the above:

C#1 12/31/10 $5000 2/11/11
This date is
calculated using the WORKDAY function.

=WORKDAY(D,F) (F=30, the number of days the
Customer
pays the bill)

In this case, "Days of the week", "Is this a Holiday", and "Count as a
holiday" are marked / calculated as follows:


12/31 1/1 1/2 1/3

FRI SAT SUN MON

NO NO YES YES

NO NO NO 1

What I need to do some how look at the "Count as Holiday" line and add
up the 1's within a date range and add the total to the WORKDAY
function calculation in column G. So, this client pays me in 30 days,
and I know that Jan 3 is a legal holiday (New Years Day), and Jan 17
is Martin Luther King Day". The count should be 2. Therefore, my
"Expected Pay Date" in column G should not be 2/11/11 (a Friday), but
should be the following Wednesday, 2/15/11.

How can I do this?

Thank you for your help!

On Feb 9, 11:01*pm, Ron Rosenfeld <r...@nospam.net> wrote:
> On Wed, 9 Feb 2011 13:58:06 -0800 (PST), tr2yhb <tr2...@yahoo.com> wrote:
> >I have a client that pays me in 30 business / working day. *I have an
> >excel file that I have laid out in the following manner:
>
> >A * * * * * * * * *D * * * * * * * * *E
> >G * * * * * * * * * * *H --> DW
> > * * * * * * * * * * * * * * * * * ** * * * * * * * * *Expected
> >Customer * * Invoice * * * * *Inv * * * * Pay
> >Name * * * * * Date * * * * * * *Amt * * *Date * * * * * * * * *Dates
> >starting with 12/31/10 thru 4/29-11
>
> >Day of the week * (calculated) * * * * * * * * * ** * * * * * *This
> >line tells me if the above date is a SUN,
>
> >MON, TUE, --> SAT
> >Is this a holiday? * *(Manually changed) * * * * * * ** * * This line
> >has a YES or NO
> >Count as a holiday * (calculated) * * * * * * * * * * * * * * * *If
> >this day is a MON-FRI and is a YES for
>
> >"Is this a holiday, it has a value of 1,
>
> >or if false, NO.
>
> >Then I list the client below all of the above:
>
> >C#1 * * * * * *12/31/10 * * * *$5000 * * 2/11/11
> > * * * * * * * * * * * * * * * * * ** * * * * * * * * * This date is
> >calculated using the WORKDAY function.
>
> >=WORKDAY(D,F) *(F=30, the number of days the
> > * * * * * * * * * * * * * * * * * ** * * * * * * * * * *Customer
> >pays the bill)
>
> >In this case, "Days of the week", "Is this a Holiday", and "Count as a
> >holiday" are marked / calculated as follows:
>
> >12/31 * * * *1/1 * * * * 1/2 * * * * *1/3
>
> >FRI * * * * * *SAT * * * SUN * * * *MON
>
> >NO * * * * * NO * * * * YES * * * * YES
>
> >NO * * * * * NO * * * * *NO * * * * *1
>
> >What I need to do some how look at the "Count as Holiday" line and add
> >up the 1's within a date range and add the total to the WORKDAY
> >function calculation in column G. *So, this client pays me in 30 days,
> >and I know that Jan 3 is a legal holiday (New Years Day), and Jan 17
> >is Martin Luther King Day". *The count should be 2. *Therefore, my
> >"Expected Pay Date" in column G should not be 2/11/11 (a Friday), but
> >should be the following Wednesday, 2/15/11.
>
> >How can I do this?
>
> >Thank you for your help!
>
> Set up a list of Holidays someplace, and use that as the optional Holidays argument in the WORKDAY function.

Thank you.