Formula assistance (date +2 and Holidays)

Hey all,

I've gotten some great help (and learned alot) on formulas from a certian
Tech Forum, but I think we're possibly stuck on my problem now.. I was
hoping you guys and gals on the news group would be able to shed some light


Here's what i'm trying to do .. I'd like to put the date in a field and have
other fields populate with projected dates +2 days. Only counting work days
so if the start date fell on a monday it would populate the next field on a
wednesday .. but if it fell on a friday it would skip the weekend and then
add two days which would be the tuesday. In addition to identifying stat
holiday and skipping them as well .. In short I want to add + 2 BUSINESS
DAYS only..

Here's what we've come up with so far ..

I've create a range on a seperate tab called stats, (the range and tab are
both called stats) this range has all the stat holidays till the begining of
2008. The Formula i'm using is ..

=IF(A2<>"",WORKDAY(A2,2,Stats),"")

The result I get in the field is....... #NAME?


Any tips would greatly be appreciated Also I'm using Excel 2003

Thanks.

Try using Networkdays(1st date,2nd date,holidays). For more info look up
NETWORKDAYS in help on excel

All the best
Philip

"Andre" <~remove~a_zavaglia@gfrnutritionals.com> wrote in message
news:uU5Y%23LvkFHA.3568@tk2msftngp13.phx.gbl...
> Hey all,
>
> I've gotten some great help (and learned alot) on formulas from a certian
> Tech Forum, but I think we're possibly stuck on my problem now.. I was
> hoping you guys and gals on the news group would be able to shed some
> light
>
>
> Here's what i'm trying to do .. I'd like to put the date in a field and
> have
> other fields populate with projected dates +2 days. Only counting work
> days
> so if the start date fell on a monday it would populate the next field on
> a
> wednesday .. but if it fell on a friday it would skip the weekend and then
> add two days which would be the tuesday. In addition to identifying stat
> holiday and skipping them as well .. In short I want to add + 2 BUSINESS
> DAYS only..
>
> Here's what we've come up with so far ..
>
> I've create a range on a seperate tab called stats, (the range and tab are
> both called stats) this range has all the stat holidays till the begining
> of
> 2008. The Formula i'm using is ..
>
> =IF(A2<>"",WORKDAY(A2,2,Stats),"")
>
> The result I get in the field is....... #NAME?
>
>
> Any tips would greatly be appreciated Also I'm using Excel 2003
>
> Thanks.
>
>
>

"Andre" <~remove~a_zavaglia@gfrnutritionals.com> wrote in
news:uU5Y#LvkFHA.3568@tk2msftngp13.phx.gbl:

> =IF(A2<>"",WORKDAY(A2,2,Stats),"")
>
> The result I get in the field is....... #NAME?
>
>
> Any tips would greatly be appreciated Also I'm using Excel 2003
>
> Thanks.
>
>
>
>

Is Analysis Toolpak available under Tools/Add-ins?
If not you have to add/activate it to get the WORKDAY function available.

=IF(B10<>"",WORKDAY(B10,2,stats),"")
works for me whether the name is typed as Stats or stats
Does the name "stats" exist in your list?
i.e can you select "stats" in the dropdown list above column A
Does the formula work if you enter the area as for
instance ----stats!(a2:a13)-----
in stead of stats.
Does the formula WORKDAY(A2,2,Stats) work?

--
Greetings from New Zealand
Bill K

"Andre" <~remove~a_zavaglia@gfrnutritionals.com> wrote in message
news:uU5Y%23LvkFHA.3568@tk2msftngp13.phx.gbl...
> Hey all,
>
> I've gotten some great help (and learned alot) on formulas from a certian
> Tech Forum, but I think we're possibly stuck on my problem now.. I was
> hoping you guys and gals on the news group would be able to shed some
> light
>
>
> Here's what i'm trying to do .. I'd like to put the date in a field and
> have
> other fields populate with projected dates +2 days. Only counting work
> days
> so if the start date fell on a monday it would populate the next field on
> a
> wednesday .. but if it fell on a friday it would skip the weekend and then
> add two days which would be the tuesday. In addition to identifying stat
> holiday and skipping them as well .. In short I want to add + 2 BUSINESS
> DAYS only..
>
> Here's what we've come up with so far ..
>
> I've create a range on a seperate tab called stats, (the range and tab are
> both called stats) this range has all the stat holidays till the begining
> of
> 2008. The Formula i'm using is ..
>
> =IF(A2<>"",WORKDAY(A2,2,Stats),"")
>
> The result I get in the field is....... #NAME?
>
>
> Any tips would greatly be appreciated Also I'm using Excel 2003
>
> Thanks.
>
>
>

Thanks!! .. I did find this under help, and that did resolve my problem
after installing the Analysis Toolpak..

My problem changes a bit now .. as i make it more complicated

Here's what i've done in a particular row

N = Start date (july 25th)(manually entered)

O = =IF(N7<>"",WORKDAY(N7,4,stats),"") which = 7/29/2005

P = =IF(O7<>"",WORKDAY(O7,2,stats),"") which = 8/3/2005

Q = =IF(P7<>"",WORKDAY(P7,2,stats),"") which = 8/5/2005

R = =IF(Q7<>"",WORKDAY(Q7,10,stats),"")which = ####### ?


So I've successfully made it populate (with your help) from one start date
into three other fields each using the field before .. the first one is 4
days, 2 days, 2 days then the last one is 10 days .. and it comes up ######
instead of 08-19-05 like it should.. If i make the last formula a 2day
formula it works out. but anything over 2 #####'s


"Dodo2u" <dodo2u@-takethisNOSPAMout-freemail.nl> wrote in message
news:Xns96A14FD7EB8dodo2ufreemailnl@62.179.104.135...
> "Andre" <~remove~a_zavaglia@gfrnutritionals.com> wrote in
> news:uU5Y#LvkFHA.3568@tk2msftngp13.phx.gbl:
>
> > =IF(A2<>"",WORKDAY(A2,2,Stats),"")
> >
> > The result I get in the field is....... #NAME?
> >
> >
> > Any tips would greatly be appreciated Also I'm using Excel 2003
> >
> > Thanks.
> >
> >
> >
> >
>
> Is Analysis Toolpak available under Tools/Add-ins?
> If not you have to add/activate it to get the WORKDAY function available.

Boy don't I feel sheepish .. aparently it means just make the column wider
LOL

Thanks for the help guys!! :D


"Andre" <~remove~a_zavaglia@gfrnutritionals.com> wrote in message
news:uY$IamvkFHA.2644@TK2MSFTNGP09.phx.gbl...
> Thanks!! .. I did find this under help, and that did resolve my problem
> after installing the Analysis Toolpak..
>
> My problem changes a bit now .. as i make it more complicated
>
> Here's what i've done in a particular row
>
> N = Start date (july 25th)(manually entered)
>
> O = =IF(N7<>"",WORKDAY(N7,4,stats),"") which = 7/29/2005
>
> P = =IF(O7<>"",WORKDAY(O7,2,stats),"") which = 8/3/2005
>
> Q = =IF(P7<>"",WORKDAY(P7,2,stats),"") which = 8/5/2005
>
> R = =IF(Q7<>"",WORKDAY(Q7,10,stats),"")which = ####### ?
>
>
> So I've successfully made it populate (with your help) from one start date
> into three other fields each using the field before .. the first one is 4
> days, 2 days, 2 days then the last one is 10 days .. and it comes up
######
> instead of 08-19-05 like it should.. If i make the last formula a 2day
> formula it works out. but anything over 2 #####'s
>
>
> "Dodo2u" <dodo2u@-takethisNOSPAMout-freemail.nl> wrote in message
> news:Xns96A14FD7EB8dodo2ufreemailnl@62.179.104.135...
> > "Andre" <~remove~a_zavaglia@gfrnutritionals.com> wrote in
> > news:uU5Y#LvkFHA.3568@tk2msftngp13.phx.gbl:
> >
> > > =IF(A2<>"",WORKDAY(A2,2,Stats),"")
> > >
> > > The result I get in the field is....... #NAME?
> > >
> > >
> > > Any tips would greatly be appreciated Also I'm using Excel 2003
> > >
> > > Thanks.
> > >
> > >
> > >
> > >
> >
> > Is Analysis Toolpak available under Tools/Add-ins?
> > If not you have to add/activate it to get the WORKDAY function
available.
>
>

On Wed, 27 Jul 2005 14:38:39 -0700, "Andre"
<~remove~a_zavaglia@gfrnutritionals.com> wrote:

>Here's what i'm trying to do .. I'd like to put the date in a field and have
>other fields populate with projected dates +2 days. Only counting work days
>so if the start date fell on a monday it would populate the next field on a
>wednesday .. but if it fell on a friday it would skip the weekend and then
>add two days which would be the tuesday. In addition to identifying stat
>holiday and skipping them as well .. In short I want to add + 2 BUSINESS
>DAYS only..

I realize you're already on to the next problem. I've come looking for
a solution that includes accounting for holidays. Along the way, I
solved the problem you're asking for, and I'll throw it out here for
future searchers, with the following formula:

=IF(MOD(D71-$B70, 7)<2, (D71-($B70+2)),(D71-$B70))

D71 has the delivery date, and I work backwards (up the sheet) from
there. B70 has the number of day's I want to allow for the previous
step. MOD saves the remainder of the division. When I divide by 7, I
can tell what day of the week the result is: 0=Saturday, 1=Sunday. So
if MOD is less than 2, I bump it by 2 days and if MOD is not less than
2, I know I've landed on a weekday.

hth

Jim

On Thu, 08 Sep 2005 17:33:48 GMT, Jim Dornbos <usenet@work.null.com> wrote:

>On Wed, 27 Jul 2005 14:38:39 -0700, "Andre"
><~remove~a_zavaglia@gfrnutritionals.com> wrote:
>
>>Here's what i'm trying to do .. I'd like to put the date in a field and have
>>other fields populate with projected dates +2 days. Only counting work days
>>so if the start date fell on a monday it would populate the next field on a
>>wednesday .. but if it fell on a friday it would skip the weekend and then
>>add two days which would be the tuesday. In addition to identifying stat
>>holiday and skipping them as well .. In short I want to add + 2 BUSINESS
>>DAYS only..
>
>I realize you're already on to the next problem. I've come looking for
>a solution that includes accounting for holidays. Along the way, I
>solved the problem you're asking for, and I'll throw it out here for
>future searchers, with the following formula:
>
>=IF(MOD(D71-$B70, 7)<2, (D71-($B70+2)),(D71-$B70))
>
>D71 has the delivery date, and I work backwards (up the sheet) from
>there. B70 has the number of day's I want to allow for the previous
>step. MOD saves the remainder of the division. When I divide by 7, I
>can tell what day of the week the result is: 0=Saturday, 1=Sunday. So
>if MOD is less than 2, I bump it by 2 days and if MOD is not less than
>2, I know I've landed on a weekday.
>
>hth
>
>Jim

Use the WORKDAY worksheet function with the optional Holidays parameter.


--ron