Results 1 to 6 of 6
Thread: Efficient Formula (2003)

20081027, 07:31 #1
 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

20081027, 07:58 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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 addin 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?

20081027, 08:25 #3
 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

20081027, 08:26 #4
 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))

20081027, 08:33 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20081027, 13:13 #6
 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