1. ## 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

2. ## 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. ## 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. ## 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. ## 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. ## 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
•