Formula assistance (date +2 and Holidays)

  • Thread starter Thread starter Andre
  • Start date Start date
A

Andre

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
 
=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.
Click to expand...

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?
 
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
 
Boy don't I feel sheepish .. aparently it means just make the column wider
LOL

Thanks for the help guys!! :D
 
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..
Click to expand...

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
 
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
Click to expand...

Use the WORKDAY worksheet function with the optional Holidays parameter.


--ron
 
Back
Top