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.

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,holi days))
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"seltzer" wrote:

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.

On Wed, 22 Oct 2008 14:07:01 -0700, seltzer
wrote:

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

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.
--
Thanks,
Shane Devenshire


"seltzer" wrote:

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.

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


"John C" wrote:

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,holi days))
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"seltzer" wrote:

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.

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

"Ron Rosenfeld" wrote:

On Wed, 22 Oct 2008 14:07:01 -0700, seltzer
wrote:

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

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



"ShaneDevenshire" wrote:

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.
--
Thanks,
Shane Devenshire


"seltzer" wrote:

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.

Yep, sorry, NETWORKDAYS is (startdate,enddate,[holidays]), so I should have
put:
....NETWORKDAYS($C2,$C2+10,holidays)...
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"seltzer" wrote:

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


"John C" wrote:

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,holi days))
--
** John C **
Please remember, if your question is answered, to check the YES box below.
It helps everyone.


"seltzer" wrote:

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.