Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Networkdays
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Networkdays
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 "StephanieH" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Networkdays
On Feb 28, 7:53 pm, StephanieH
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NETWORKDAYS | Excel Worksheet Functions | |||
#NAME when using NETWORKDAYS | Excel Discussion (Misc queries) | |||
NETWORKDAYS | Excel Discussion (Misc queries) | |||
networkdays | Excel Worksheet Functions | |||
networkdays | Excel Worksheet Functions |