Workday function for 6 day workweek and negative/subtract workdays

sleepyshan2

New Member
Joined
Jul 17, 2009
Messages
3
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?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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))
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top