Results 1 to 6 of 6
  1. #1
    New Lounger Bigshrimp60's Avatar
    Join Date
    Mar 2011
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Formula to find next business day

    I have created several templates that create a timeline based on variable events. The user can enter any date and the timeline auto-populates with the future day and date for that event. I need help creating a formula that will advance the day(s) to the next business day if the timeline day falls on a weekend. I have included a sample of my template. All help is appreciated. Thanks.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    For the first row (which does not use the elapsed day, E10:
    =IF($C$7="","",$C$7+(WEEKDAY($C$7)=1)+2*(WEEKDAY($ C$7)=7))

    For e11:
    =IF($C$7="","",$C$7+A11+(WEEKDAY($C$7+A11)=1)+2*(W EEKDAY($C$7+A11)=7))

    Copy E11 to E12:E20

    Steve

  3. #3
    New Lounger Bigshrimp60's Avatar
    Join Date
    Mar 2011
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Formula to find next business day

    Thank you for the quick response. I used the formula above and E11 gives me a FALSE. E12:E20 seem to be OK. Did I do something wrong? I attached a new sample.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Expand the height of the formula bar and you should see the problem: you have enetered the formula into the cell 3 times. Remove the last 2. [You essentially have something like =A=A=A, which excel interprets from left to right. So it checks as: =(A=A)=A which solves =(TRUE)=A then since TRUE does not equal A, the equation becomes FALSE.]

    Steve

  5. The Following User Says Thank You to sdckapr For This Useful Post:

    Bigshrimp60 (2012-12-28)

  6. #5
    New Lounger Bigshrimp60's Avatar
    Join Date
    Mar 2011
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Not sure how that happened, but many, many, many thanks. It works perfectly!

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    You asked for for a formula to advance the timeline day to the next business day if the timeline day falls on a weekend.
    Maybe your business doesn't recognise public holidays.

    Steve's solution will advance the day to the next Monday, if the timeline day falls on a weekend.
    But that might not be the next business day if you are a Federal employee.

    Unless in 2013 you work on Washington's birthday, Memorial day, Labor Day, Columbus Day, Veterans Day etc.

    Federal law (5 U.S.C. 6103) establishes public holidays for Federal employees.
    Most Federal employees work on a Monday through Friday schedule.
    For these employees, when a holiday falls on a nonworkday -- Saturday or Sunday -- the holiday usually
    is observed on Monday (if the holiday falls on Sunday) or Friday (if the holiday falls on Saturday).

    Now, as an Englishman, may I suggest you use the WORKDAY function as per the attached sample file, which allows you to specify a h)oliday calendar (and any company-specific non-workdays).

    I have included a Federal holiday calendar for 2012-2014 (which you can update as required).
    As an example, using a date of 4th Feb 2013 as your start date in cell $C$7, Steve's solution would have you filing you Reply Brief on Thursday 4th July.
    And even I know that nobody likes working on THAT day.
    If you are in the USA that is.

    zeddy
    Attached Files Attached Files

Posting Permissions

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