+ Reply to Thread
Results 1 to 10 of 10

Ignoring weekends & holidays-NETWORKDAYS?

  1. #1
    Registered User
    Join Date
    01-07-2005
    Posts
    20

    Ignoring weekends & holidays-NETWORKDAYS?

    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.

  2. #2
    Peo Sjoblom
    Guest

    Re: Ignoring weekends & holidays-NETWORKDAYS?

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

    --

    Regards,

    Peo Sjoblom

    "chaminod" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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.
    >
    >
    > --
    > chaminod
    > ------------------------------------------------------------------------
    > chaminod's Profile:

    http://www.excelforum.com/member.php...o&userid=18163
    > View this thread: http://www.excelforum.com/showthread...hreadid=493488
    >




  3. #3
    Registered User
    Join Date
    01-07-2005
    Posts
    20

    But how?

    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.

  4. #4
    William Horton
    Guest

    RE: Ignoring weekends & holidays-NETWORKDAYS?

    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

    "chaminod" wrote:

    >
    > 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.
    >
    >
    > --
    > chaminod
    > ------------------------------------------------------------------------
    > chaminod's Profile: http://www.excelforum.com/member.php...o&userid=18163
    > View this thread: http://www.excelforum.com/showthread...hreadid=493488
    >
    >


  5. #5
    jaf
    Guest

    Re: Ignoring weekends & holidays-NETWORKDAYS?

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

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

    =252


    --
    John
    johnf202 at hot mail dot com


    "chaminod" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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.
    >
    >
    > --
    > chaminod
    > ------------------------------------------------------------------------
    > chaminod's Profile:
    > http://www.excelforum.com/member.php...o&userid=18163
    > View this thread: http://www.excelforum.com/showthread...hreadid=493488
    >




  6. #6
    HowardC
    Guest

    RE: Ignoring weekends & holidays-NETWORKDAYS?

    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!

    "William Horton" wrote:

    > 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
    >
    > "chaminod" wrote:
    >
    > >
    > > 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.
    > >
    > >
    > > --
    > > chaminod
    > > ------------------------------------------------------------------------
    > > chaminod's Profile: http://www.excelforum.com/member.php...o&userid=18163
    > > View this thread: http://www.excelforum.com/showthread...hreadid=493488
    > >
    > >


  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    You're on the right track

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

    A$1:A$11

  8. #8
    HowardC
    Guest

    Re: Ignoring weekends & holidays-NETWORKDAYS?

    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" wrote:

    >
    > You're on the right track
    >
    > If you want to drag down and keep the refs the same use
    >
    > A$1:A$11
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=493488
    >
    >


  9. #9
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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)

  10. #10
    HowardC
    Guest

    Re: Ignoring weekends & holidays-NETWORKDAYS?

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

    "daddylonglegs" wrote:

    >
    > 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)
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=493488
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1