Workday Formula?

[Guest]

I've been trying to develop a production schedule in Excel. I'd like to put
an end date and have Excel calculate each activities' start date based on the
number of days needed. I can do a simple subtraction formula (end date-#of
days) however that does not take into account weekends and holidays. I tried
the Workday function but that didn't work.

Suggestions are most appreciated. Thank you!

 

[Peo Sjoblom]

What didn't work? Did you get errors or was the result incorrect?
It's part of the ATP (Analysis ToolPak) that comes with Excel/Office but it
won't install using the default installation and if it's not installed you
will get a name error. If so just go to tools>add-ins and select it, then
plop in the installation CD when prompted

 

[Guest]

If I use the 'Workday' function, I get an incorrect result. If I use
'NetWorkday', I get an error. I have the end date at the bottom of the
spreadsheet and rows with number of days for completion of the project. When
I use the formula 'Workday(end date - #of days)', I get #NUM error. If I use
the formula 'Workday(end date,#of days)', I get an incorrect result.

Many thanks!

 

[Peo Sjoblom]

Is it one day off? Both WORKDAY and NETWORKDAYS include the dates meaning
that if you would use

=WORKDAY(TODAY(),10)

then today's date would be included in the count, some people consider that
incorrect. Also the NUM error derives from an impossible calculation for
example

=WORKDAY(TODAY()-100000)


You might also want to check under tools>options>transition and make sure
you don't have anything checked there since it will screw up the dates and
make any WORKDAY formula with -10 return a #NUM! error

 

[Guest]

I think you just have a minor syntax / typo issue.

But, to clear things up before we go any further:
WORKDAY works out what date is X days after (minus = before) a given start
date, ignoring wekends and optional holidays
NETWORKDAYS does the opposite - works out how many days are between two
given dates, excluding weekends and hols.

So, it sounds like WORKDAY is what you need, but you want to go backwards.
That's fine, but your arguments still need to be separated by a comma, so you
want something like:
=WORKDAY(EndDate, -NumDays, [Hols])
NOTE the comma after EndDate - you can't do "EndDate-NumDays" or there are
too few arguments (or rather the first holiday in your list would get
included as NumDays, and may well be out of range)

(apologies if your missed comma was a typo in the forum, rather than in your
actual spreadsheet formula)

As mentioned above, watch out for whether you include or exclude the end
date and modify accordingly (this depends on your business model, there's no
'right' answer)

As an aside - make sure you use a named range to point at your list of
holidays so you can easily modify to keep up with changes as you add for
future years.

 

[Guest]

Great post but I am using the following and getting an error.

=WORKDAY(H19, -10, [hols]) and I have created range HOLS which are all the
cells with the Holidays.

Fomula works fine without HOLS but when I add it I get the error.

Help Please.
--
Brian

I think you just have a minor syntax / typo issue.

But, to clear things up before we go any further:
WORKDAY works out what date is X days after (minus = before) a given start
date, ignoring wekends and optional holidays
NETWORKDAYS does the opposite - works out how many days are between two
given dates, excluding weekends and hols.

So, it sounds like WORKDAY is what you need, but you want to go backwards.
That's fine, but your arguments still need to be separated by a comma, so you
want something like:
=WORKDAY(EndDate, -NumDays, [Hols])
NOTE the comma after EndDate - you can't do "EndDate-NumDays" or there are
too few arguments (or rather the first holiday in your list would get
included as NumDays, and may well be out of range)

(apologies if your missed comma was a typo in the forum, rather than in your
actual spreadsheet formula)

As mentioned above, watch out for whether you include or exclude the end
date and modify accordingly (this depends on your business model, there's no
'right' answer)

As an aside - make sure you use a named range to point at your list of
holidays so you can easily modify to keep up with changes as you add for
future years.
--
Adam Vero
MCP, MOS Master, MLSS, CWNA
http://veroblog.wordpress.com
http://www.meteorit.co.uk

I've been trying to develop a production schedule in Excel. I'd like to put
an end date and have Excel calculate each activities' start date based on the
number of days needed. I can do a simple subtraction formula (end date-#of
days) however that does not take into account weekends and holidays. I tried
the Workday function but that didn't work.

Suggestions are most appreciated. Thank you!

 

[Roger Govier]

Hi

You don't need the square brackets around hols.
=WORKDAY(H19, -10, hols)
--
Regards

Roger Govier

Great post but I am using the following and getting an error.

=WORKDAY(H19, -10, [hols]) and I have created range HOLS which are
all the
cells with the Holidays.

Fomula works fine without HOLS but when I add it I get the error.

Help Please.
--
Brian

I think you just have a minor syntax / typo issue.

But, to clear things up before we go any further:
WORKDAY works out what date is X days after (minus = before) a given
start
date, ignoring wekends and optional holidays
NETWORKDAYS does the opposite - works out how many days are between
two
given dates, excluding weekends and hols.

So, it sounds like WORKDAY is what you need, but you want to go
backwards.
That's fine, but your arguments still need to be separated by a
comma, so you
want something like:
=WORKDAY(EndDate, -NumDays, [Hols])
NOTE the comma after EndDate - you can't do "EndDate-NumDays" or
there are
too few arguments (or rather the first holiday in your list would get
included as NumDays, and may well be out of range)

(apologies if your missed comma was a typo in the forum, rather than
in your
actual spreadsheet formula)

As mentioned above, watch out for whether you include or exclude the
end
date and modify accordingly (this depends on your business model,
there's no
'right' answer)

As an aside - make sure you use a named range to point at your list
of
holidays so you can easily modify to keep up with changes as you add
for
future years.
--
Adam Vero
MCP, MOS Master, MLSS, CWNA
http://veroblog.wordpress.com
http://www.meteorit.co.uk

I've been trying to develop a production schedule in Excel. I'd
like to put
an end date and have Excel calculate each activities' start date
based on the
number of days needed. I can do a simple subtraction formula (end
date-#of
days) however that does not take into account weekends and
holidays. I tried
the Workday function but that didn't work.

Suggestions are most appreciated. Thank you!