Adding days to the WORKDAY function

  • Thread starter Thread starter tr2yhb
  • Start date Start date
T

tr2yhb

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!
 
Back
Top