Calculating future dates using WORKDAY & specific requirements

[seltzer]

As part of project management duties, I need to continually update a table
that contains various Visit Dates for sites and when the various reports are
due.
Column C contains a date as it is scheduled meaning some of the rows of
pending visits will not yet contain a date in that cell or if the visit is to
be cancelled, the cell will contain NA. The first draft of the report is due
10 days after the Scheduled Visit Date shown in Column C. In the past, I
used the following formula putting the due report date in column D:
=IF(OR($C2="",$C2="NA"),"",($C2+10)) Now the boss wants the draft due 10
Workdays after the Scheduled Visit Date. I have lots of other columns of
data in this table so I'd prefer to keep all of the required parts of the
formula within the one formula if possible. At this time, they aren't
concerned about excluding standard holidays although I wouldn't mind knowing
how to create that formula as well.
Hope someone can help.

 

[John C]

NETWORKDAYS
If you have xl2003 or older, you will need to ensure that the Analysis
Tool-Pak add-in is included.
Your formula would then be:
=IF(OR($C2="",$C2="NA"),"",NETWORKDAYS($C2,10))
And if you want to get really fancy, say you have a HOLIDAY list somewhere
that you continue to add holidays on to, and you don't want to include it,
your formula would then be:
=IF(OR($C2="",$C2="NA"),"",NETWORKDAYS($C2,10,holidays))

 

[Ron Rosenfeld]

As part of project management duties, I need to continually update a table
that contains various Visit Dates for sites and when the various reports are
due.
Column C contains a date as it is scheduled meaning some of the rows of
pending visits will not yet contain a date in that cell or if the visit is to
be cancelled, the cell will contain NA. The first draft of the report is due
10 days after the Scheduled Visit Date shown in Column C. In the past, I
used the following formula putting the due report date in column D:
=IF(OR($C2="",$C2="NA"),"",($C2+10)) Now the boss wants the draft due 10
Workdays after the Scheduled Visit Date. I have lots of other columns of
data in this table so I'd prefer to keep all of the required parts of the
formula within the one formula if possible. At this time, they aren't
concerned about excluding standard holidays although I wouldn't mind knowing
how to create that formula as well.
Hope someone can help.

....,(WORKDAY($C2,10)))

If you try this and receive a #NAME error, look at HELP for the WORKDAY
function for instructions as to how to resolve this.
--ron

 

[ShaneDevenshire]

Hi,

Try
=IF(ISNUMBER(C2),WORKDAY(C2,10),"")

or

=IF(ISNUMBER(C2),WORKDAY(C2,10,G1:G4),"")

Where G1:G4 contain a list of holidays.

Just a reminder, the WORKDAY function is an Analysis ToolPak function which
means you must attach the ATP, Choose Tools, Add-ins, and check Analysis
ToolPak.

If this helps, please check the Yes button.

 

[seltzer]

When I add the new section NETWORKDAYS($C2,10)) I get ######### indicating a
negative date. The dates are entered as 20-Mar-07.
When I add the new section WORKDAY($C3,10)) I get the correct answer.

Thank you,
Seltzer

 

[seltzer]

Thanks Ron - I switched to using the WORKDAY term rather than NETWORKDAYS
after reading your post & that seemed to be the trick.

 

[seltzer]

After using the WORKDAY($C2,10))) for a series of rows, I ran into some visit
dates that hadn't been firmed up and either were empty cells or had TBD in
them. The formula gave an error message to that which made a "messy" table
but Your formula took that into account and seems to also provide the correct
answers. So it looks like since I need to pre-set this table with the
formulas in place, I will use your suggestion. Thanks, Shane.

And thanks to all of you again for such speedy answers - As soon as I got
into work, there were all of your responses.
Seltzer

 

[John C]

Yep, sorry, NETWORKDAYS is (startdate,enddate,[holidays]), so I should have
put:
....NETWORKDAYS($C2,$C2+10,holidays)...