How do I add only business days in Excel formulas?

[Guest]

I'm trying to build schedules in Excel with anywhere between 10 and 30
scheduled tasks within the timeline of a few months. The formulas I need to
use seem like they would be pretty basic, but I realize I need some task
dates based on calendar days and some on business days. For example, I need
to allow a calendar week for people to provide comments, but then need to
print the document 2 business days later. So how do I distinguish between
counting calendar days and counting only business days? I've searched the
help options in Excel and nothing I've found seems to speak to this
particular issue. I'm also pretty new to working in Excel, so there might be
an obvious solution out there that I just don't know about.
Thanks!
Maureen

 

[Guest]

Look at the function NETWORKDAYS

 

[Guest]

I had looked at that and it seemed to me it was intended to calculate the
number of work days between dates. I'm trying to use a formula or function
that will return an actual date. For example 10/13/06 + 2 business
days=10/17/06. It's sounds like simple math, but I'm trying to create a
spreadsheet where I can plug in a couple key dates and then the rest will
generate based on formulas/functions.

 

[Guest]

I had looked at that function, but it seemed to me that it was intended to
calculate the number of work days between two dates and I'm looking for a
formula/function that will return an actual date. For example, 10/13/06 + 2
business days = 10/17/06. It sounds like simple math, but my objective is to
create a spreadsheet where I can plug in a couple of key dates and the rest
of the dates will generate based on formulas & functions. Know of anything
that could work for that?

 

[Roger Govier]

Hi Mo

You need Workday() not Networkdays
=WORKDAY(A1,2) with 13 Oct 2006 in A1 returns 17 Oct 2006

 

[Roger Govier]

Forgot to add, there is a 3rd parameter, holidays
=WORKDAY(A1,2, holidays)
or
=WORKDAY(A1,2,$G$1:$G$9)
where the named range holidays or the cells G1:G9 contain the holiday
dates

 

[Guest]

Thanks, Roger, I think you got to the heart of my issue. It seems like that
should work...I just tried it and I'm getting the #NAME? message. Could I be
doing something inherently wrong that's having a downstream effect, like date
format or something basic?

 

[Roger Govier]

Sorry Mo

I should have said you need the Analysis Toolpak loaded.
Tools>Addins>check Analysis Toollpak

 

[James Silverton]

Forgot to add, there is a 3rd parameter, holidays
=WORKDAY(A1,2, holidays)
or
=WORKDAY(A1,2,$G$1:$G$9)
where the named range holidays or the cells G1:G9 contain the
holiday dates

It does not surprise me to learn something new about Excel ,
and when thinking about this problem I came upon the Goal Seek
Tool. You can evaluate a date at a given numbers of workdays
from a start using this.

Suppose you have the start day in A1, any old guess at the end
day in B1, and C1 =NETWORKDAYS(A1,B1)

Then the Goal Seek Tool can be asked to set C1 equal to any
numerical value by changing B1. I wonder if a macro could be set
up with a loop to produce a sequential list of values.

I know I have omitted the holidays parameter in NETWORKDAYS and
I have not yet tried to set up the macro.

 

[Guest]

Yahoo, it works!!! Thank you so much! You have just helped me save so much
time for so many people. And thank you for the 3rd perameter (holidays). This
is the first time I've written into a discussion group & it's been a great
experience. Thanks everyone for your help.
Mo

 

[Roger Govier]

Hi Mo

Glad it worked for you and thanks for posting back to let us all know.