Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Efficient Formula (2003)

    I created a planning spreadsheet that provides the calendar day a product is due based on Column B. Then I adjust the due date to make sure it lands on a business day and accounts for weekends and holidays referenced on Sheet 2. I'm not so sure I created the most efficient formula though or used the best method of affecting this. Is there another way of doing this?

    Amy
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Efficient Formula (2003)

    These calculations usually use a number of working days, i.e. exclude both weekend days and holidays. The Analysis ToolPak add-in contains a function WORKDAY for this. But you appear to want to add a number of days and include weekend days but not holidays in the count. I don't think there's a function available for that.
    Any chance you can use the more conventional calculation?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Efficient Formula (2003)

    Thanks Hans. I was given column B to work with. That number will include weekend and holidays, but has to return the next workday after the weekend and/or holiday. Since there isn't a function, I was wondering if the VLOOKUP table I set up is the most efficient manner of doing it. The formulas got pretty long using the IF function.

    Amy

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Efficient Formula (2003)

    I would suggest that instead of using the Add One, Add Two, or Add Three you use their numerical equivalents of 1, 2, or 3. Then filling in the appropriate weekend added as well - 2 for Saturdays and 1 for Sundays. Non "special" days can left blank.

    Your table would look like something like this (the date formatting changed due to the macro I used to copy from Excel):
    <table border 1><td>10/11/2008</td><td>Saturday</td><td>3</td><td> </td><td>10/12/2008</td><td>Sunday</td><td>2</td><td> </td><td>10/13/2008</td><td>Monday</td><td>1</td><td>Columbus Day</td><td>10/14/2008</td><td>Tuesday</td><td> </td><td> </td><td>10/15/2008</td><td>Wednesday</td><td> </td><td> </td><td>10/16/2008</td><td>Thursday</td><td> </td><td> </td><td>10/17/2008</td><td>Friday</td><td> </td><td> </td><td>10/18/2008</td><td>Saturday</td><td>2</td><td> </td><td>10/19/2008</td><td>Sunday</td><td>1</td><td> </td><td>10/20/2008</td><td>Monday</td><td> </td><td> </td></table>


    You could then use:
    =L4+VLOOKUP(L4,Sheet2!$A$1:$C$365,3,0)

    For your C column, in the Left() function, you can keep the 3 as in "=$B$1+LEFT(B4,3)" for all of the dates as long as you don't exceed 999 days - the space between the number and the word days is ignored in the calculation.
    Or you could use:
    =$B$1+LEFT(B4,FIND(" ",B4))

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Efficient Formula (2003)

    Try Mike Barron's suggestion of using numbers instead of text in the lookup table, that avoids the need for all those IFs.

  6. #6
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Efficient Formula (2003)

    Thanks Mike. I knew there had to be a more streamlined way of doing this.

    Amy

Posting Permissions

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