Networkdays

[Guest]

I'm building a scorecard to track due dates vs completion dates. For
instance, if a report is due 3/2/07 and turned in 3/2/07, the employee gets a
5 rating. If it's published on the next workday 3/5/07 they get a 4 rating.
This part, I'm fine with. I use the formula =NETWORKDAYS(B3,C3)-1 to
determine the days between the due date and completion. I use a Vlookup
formula to then determine the rating.
RatingScale
Days Rating
0 5
1 4
2 3
3 2
4 1

Here's my delima: I'd like to create a grid so that the employee can see at
a glance what their rating will be if the report is publised on a given day.
I would list the due date in column A, then want the 4 rating date in B, 3
rating date in C, etc. For instance, if the report is due 3/2, I need to add
1 workday so that 3/5/07 appears column B and 3/6/07 in column C, etc. OR,
if 3/1 were the due date, then 3/2 in column B and 3/5 in column C, etc.

Is this possible?

 

[Guest]

How about something like:

=D2+IF(WEEKDAY(D2,2)=5,3,1) where D2 is the location of the previous date
(e.g. D2 could contain the due date and you'd put this formula in D3 and then
copy D3 to D4-D7). Of course this doesn't account for holidays and assumes
that what's in D2 is a weekday.
Will

 

[vezerid]

I'm building a scorecard to track due dates vs completion dates. For
instance, if a report is due 3/2/07 and turned in 3/2/07, the employee gets a
5 rating. If it's published on the next workday 3/5/07 they get a 4 rating.
This part, I'm fine with. I use the formula =NETWORKDAYS(B3,C3)-1 to
determine the days between the due date and completion. I use a Vlookup
formula to then determine the rating.
RatingScale
Days Rating
0 5
1 4
2 3
3 2
4 1

Here's my delima: I'd like to create a grid so that the employee can see at
a glance what their rating will be if the report is publised on a given day.
I would list the due date in column A, then want the 4 rating date in B, 3
rating date in C, etc. For instance, if the report is due 3/2, I need to add
1 workday so that 3/5/07 appears column B and 3/6/07 in column C, etc. OR,
if 3/1 were the due date, then 3/2 in column B and 3/5 in column C, etc.

Is this possible?

Assuming the ratings 4, 3, 2, 1 are in cells B1:E1 in B2 use:

=WORKDAY($A2,5-B$1,holidays)

Copy down and across.

HTH
Kostis Vezerides