Ignoring weekends & holidays-NETWORKDAYS?

[chaminod]

A couple of great people here helped me with a minor prob I had on this
one & then my boss threw something else into the mix that has me
stumped:

The scenario below works great, but the end date appears 3 months late
because it includes holidays & weekends.

I thought NETWORKDAYS would help, but I think I am mistaken. I have a
schedule of holiday dates through year 2010 off to the side in cells
N8-S18 that a workable formula could refer to.

Any suggestions will be duly praised and appreciated!

........C...................D..................... ......E.........
1......1............10/25/2005...........10/26/2005
2......2............10/27/2005...........10/29/2005
3......0............10/29/2005...........10/29/2005
4......3............10/29/2005...........11/01/2005

Column C is the duration of the project in days. Column D is the
project start date. Column E is the project end date.

As example, in D4 I'm using the formula =IF(C3=0,E3+0,E3+1). I need to
include something to make it ignore weekends & holidays.

I don't have much experience in multiple conditions - I tried some
things & made a decent mess of it.

 

[Peo Sjoblom]

NETWORKDAYS ignores weekend and holidays (holidays need to be input in the
formula)

 

[chaminod]

Glad to hear I actually was on the right track with NETWORKDAYS, but how
do I incorporate NETWORKDAYS into my =IF formula?

It needs to consider both of those conditions - the fact that weekends
& holidays don't count and whether or not to add the 1 day to the prior
date.

Thanks so much for responding - I really appreciate it.

 

[Guest]

I don't totally understand your question / scenario but both the NETWORKDAYS
and WORKDAY functions exclude weekends. They also both have optional
arguments for you to enter a range that has your companies holiday dates in
them. This should get you what you want.

WORKDAY(1/1/05, 5, 1/3/05) will return 1/10/05 because 1/1 and 1/2 are a
weekend and 1/3 is listed as a holiday. 5 working days from 1/1 = 1/10.

NETWORKDAYS(1/1/05, 1/10/05, 1/3/05) will return 5 because 1/1, 1/2, 1/8,
and 1/9 are weekends and because 1/3 is listed as a holiday. 5 working days
between the 2 dates.

Hope this helps.

Thanks,
Bill Horton

 

[jaf]

Hi,
With a list of dates (holidays) in a1:a12

=NETWORKDAYS(DATEVALUE("12/31/05"),"12/31/06",A1:A12)

=252

 

[Guest]

Instead of listing all holidays in the parens, I tried, per instructions,
listing them in individual cells, and selecting those cells, eg,
!HolidayA1:A11 The big problem is I have a long column of dates where I want
to use the WORKDAY function, but when I copy and paste the formula, or drag
it, all the way down, the A1:A:11 becomes A2:A12 in the next row, etc. I
even tried using a $ in front of the A1 and A11 but it didn't help. Help!

 

[daddylonglegs]

You're on the right track

If you want to drag down and keep the refs the same use

A$1:A$11

 

[Guest]

Thank you, thank you, daddy, I was close but not quite there...much
appreciated! What I'm really trying to do is enforce a contract which
required delivery of a circuit in "40 calendar days, but when I used "date +
40" formula it landed on weekends. Of course, now that I"m using
"workday,40,A1:A12), it's coming up with a lot MORE than 40 calendar days, so
I"m suing "Workday,29,A1:A:11). Might there be a formula that "calculates X
calendar days after a selected start date, excluding, but not adding extra
days for, weekends and holidays"?

 

[daddylonglegs]

If you want to add 40 calendar days to a date in B1, but skip to the
next workday if this would give a weekend or holiday date.....

=WORKDAY(B1+39,1,A$1:A$11)

 

[Guest]

Thank you, that is SOOOO cool! So may I ask how you gathered such expertise?