Workday function for 6 day workweek and negative/subtract workdays

[sleepyshan2]

Hello,

I have searched for hours and hours for a solution to no avail, so I'm hoping the experts here can help me figure this out!

I'm trying to calculate a start date, given an end date, lead time and holiday list, but including Saturdays as a workday.

For example,
A1 = End Date, such as 8/14/2009
B1 = lead time days, such as 4 days (I need to be able to calculate beyond a single week though, currently 4, 8 and 16 days lead time)
holidays = named range "holidays" from different worksheet/tab

desired results:
if A1 is 8/14/09 and B1 is 4, 8/10/09
if A1 is 8/11/09 and B1 is 4, 8/6/09
if A1 is 7/7/09 and B1 is 4, 7/1/09 (given that 7/4/09 is in holidays named range)

Without the Saturday factor, I would use the following formula:
=WORKDAY(A1,-B1,holidays)

I've found the following array formula that is a great alternative to WORKDAY when including Saturdays, and it works great for adding to the date in A1, but if I put a negative number in B1 in this formula, it gives a #REF! error, and I don't follow the logic clearly enough to adjust myself

{=A1+SMALL(IF((WEEKDAY(A1+ROW(INDIRECT("1:"&B1*10)))>1)*ISNA(MATCH(A1+ROW(INDIRECT("1:"&B1*10)),holidays,0)),ROW(INDIRECT("1:"&B1*10))),B1)}



It does not matter to me whether the negative/subtraction is built into the formula or entered in B1 as "-4"


Can anyone help me?

 

[xld]

Try

=A1+SIGN(b1)*SMALL(IF((WEEKDAY(A1+SIGN(b1)*(ROW(INDIRECT("1:"&ABS(b1)*10))))={1,2,3,4,5,6})*
ISNA(MATCH(A1+SIGN(b1)*(ROW(INDIRECT("1:"&ABS(b1)*10))),holidays,0)),ROW(INDIRECT("1:"&ABS(b1)*10))),ABS(b1))

 

[sleepyshan2]

Bless you!!! (and wow you're fast!)

It'll take me hours to work through the formula layers to understand for later, but all of my test entries work perfectly. Thank you so much!

 

[sleepyshan2]

Apparently the dates I used in testing last week were not Thursdays, which cause a problem with my example of subtracting 4 business days. Ultimately the problem seems to be that the formula correctly excludes Sundays and includes Saturdays, unless the formula result ends on a Sunday, and then it will still provide Sunday's date.

For example:
in using date of 8/13/09 in A1 and -4 in B1, it will return 8/9/09, a Sunday. If -16 is in B1, it will return 7/26/09, also a Sunday. However, if -8 is in B1 it will correctly return 8/4/09.

Although I could wrap a second formula around the result, to determine if it's a Sunday and then subtract another day, that causes a problem with having to check the holiday list again as well.

For example
in using date of 7/9/09 in A1 and -4 in B1, it will return 7/5/09, a Sunday. If I subtract a day it will give me 7/4, which is a holiday.

Any additional ideas? Or is a second layer of formulas evaluating the "landing date" still my best option?