workday, but even more flexible

[panostis]

Hello there, =WORKDAY(E3+5,1,G2:G3) is really helpful. It's a way to negate the obsessive compulsion of "workday" to not include weekends when counting (I suppose there could be cases when this would be useful too). Thanks for that, and many other suggestions I have found on your site from time to time.
Would you be so kind as to offer your invaluable knowledge once again please?
I need =WORKDAY(E3+5,1,G2:G3) keep doing what it does (ie count weekends and holidays when adding days, but jump to very next working day if you reach the end of your calculation on them), but I also need it to be able to completely ignore/exclude/don't count at all say a month or predefined ranges of dates (in other words to behave as these aforementioned ranges do not exist at all.
So the formula should act as if a range (let's say August or some consecutive dates in A2:A5 & B2:B5) don't exist at all.
That is to say the formula should not include those dates when counting at 31/7 (the very last day when counting) and resume counting days at 1/9.
the same goes for the predefined ranges of consecutive dates in A2:A5 & B2:B5). They are to be excluded completely from counting and landing similarly to August.
What is more the resulting date cannot be (ie August or one of those predefined consecutive dates in ranges A2:A5 & B2:B5), but I guess this goes without saying.
In sort I would like to include weekends and holidays in my calculation (ie count them when adding days, but go to next working day if my calculation ends on them) and at the same time define some predefined ranges which should be ignored/skipped completely when counting as if they didn't exist at all (basically stop counting at the beginning of a range of dates and resume counting just after the last date in that range).
I would like to thank you for any ideas you may come up with as well as for the time you might spend doing it.
I've searched and searched for an answer and tried ways to accomplish the above but I am missing the advanced knowledge to do it on my own.
Sincerely,
Panos

a more flexible workday.xlsx
	A	B	C	D	E	F	G	H	I
1	Ignore dates below completely (don't count/don't land on them)	Ignore dates below completely (don't count/don't land on them)2	Ignore August completely (don't count/don't land on a date in August)	holidays to be included when counting but then if the result lands on them go to next working	Column3	some examples to demonstrate what I mean	Column4	Column5	Column6
2	02/07/2024	17/07/2024	01/08/2024	23/07/2024
3	03/07/2024	18/07/2024	02/08/2024	29/07/2024	start date	add days	landing date
4	04/07/2024	19/07/2024	03/08/2024		01/07/2024	22	24/07/2024	Τετάρτη	need this to be 31/7/2024 (ie not count blue at all! Start counting from 6/7/ to 16/7 =11days. then stop again at 17/7-20/7 and resume at 21/7 concluding 22 days at 31/7)
5	05/07/2024	20/07/2024	04/08/2024		01/07/2024	2	03/07/2024	Τετάρτη	need this to be 8/7/2024 (ie not count blue at all! Start counting two days at 6/7 landing on 7/7 which is a Sunday and should go to next working day)
6			05/08/2024		01/07/2024	18	19/07/2024	Παρασκευή	need this to be 30/7/2024 (ie not count blue at all! Start counting at 6/7 to 16/7 = 11days then resume counting the remaining 7 days at 21/7 reaching 27/7 which is a Saturday and should go on 29/7 monday but again this is a holiday, which we count normally but go to next working day)
7			06/08/2024		01/07/2024	30	31/07/2024	Τετάρτη	need this to be 9/9/2024 (ie not count blue at all! don't count August at all! Count weekends, count orange holidays but go to next working day if it lands on them
8			07/08/2024
9			08/08/2024
10			09/08/2024
11			10/08/2024
12			11/08/2024						this works for weekends and holidays (ie it counts them when calculating but if it lands on them skips to next working day. But I cannot make it exclude/ignore completely from counting predefined ranges)
13			12/08/2024					A	=WORKDAY($G4+$H4-1;1;$D$2:$D$3)
14			13/08/2024
15			14/08/2024						this only works for many prefefined blue ranges (ie excludes/ignores them completely from counting when calculating. Cannot put weekends and orange holidays in it (ie count weekends and orange holidays but go to next working day if it lands on them)
16			15/08/2024					B	=WORKDAY.INTL(G4;H4;"0000000";SMALL((A2:A5;B2:B5;C2:C32);ROW(INDIRECT("1:"&COUNT(A2:A5;B2:B5;C2:C32)))))
17			16/08/2024						31/07/2024
18			17/08/2024
19			18/08/2024						is there a way to merge A and B and make it behave in a way that it combines both behaviours ?
20			19/08/2024						A) count weekends and orange holidays when calculating but go to next working day if the calculation ends on them
21			20/08/2024						B) avoid/ignore completely blue ranges. Don't even count when calculating but stop counting and resume after the range has ended and again the same for next blue range. Keep doing so until we have counted x number of days.
22			21/08/2024
23			22/08/2024
24			23/08/2024
25			24/08/2024
26			25/08/2024
27			26/08/2024
28			27/08/2024
29			28/08/2024
30			29/08/2024
31			30/08/2024
32			31/08/2024
Sheet1

Cell Formulas
Range	Formula
G4	G4	=WORKDAY($E4+$F4-1,1,$D$2:$D$3)
H4:H7	H4	=TEXT($G4,"ηηηη")
G5:G7	G5	=WORKDAY($E5+($F5-1),1,$D$2:$D$3)
I17	I17	=WORKDAY.INTL(E4,F4,"0000000",SMALL((A2:A5,B2:B5,C2:C32),ROW(INDIRECT("1:"&COUNT(A2:A5,B2:B5,C2:C32)))))

Also asked here WORKDAY on steroids

 

[KRice]

I do not quite follow what you want. If you want to exclude additional dates, you can either add them to the holidays list, or keep them separate, and VSTACK them with the holidays, like this:

MrExcel_20240229.xlsx
	E	F	G
3	start date	add days	landing date
4	7/1/2024	22	9/12/2024
Sheet3

Cell Formulas
Range	Formula
G4	G4	=WORKDAY($E4,$F4,VSTACK($D$2:$D$3,TOCOL($A$2:$C$32,,TRUE)))

But double check your end dates. If we find the working day that is 22 days beyond July 1, it is September 12 after excluding all of the extra days, holidays, and weekends.

 

[KRice]

I think I misread your problem description. Have a look at this version 2.0...we use WORKDAY.INTL to declare all days of the week as workdays, including Saturdays and Sundays, but exclude the blue cells. That allows us to determine the date some days in the future. Then we subtract one from that date and look for the next workday using the WORKDAY function, which will recognize weekend days of Saturday and Sunday, and we include the orange holidays dates in the exclusion list also. This will then either jump forward to our original landing date determined by WORKDAY.INTL, or if that landing date is a weekend or a holiday, we'll jump forward to the next working day.

MrExcel_20240229.xlsx
	E	F	G	H
3	start date	add days	landing date	landing date v2.0
4	7/1/2024	22	9/12/2024	Wed, 7/31/2024
5	7/1/2024	2	7/9/2024	Mon, 7/8/2024
6	7/1/2024	18	9/6/2024	Tue, 7/30/2024
7	7/1/2024	30	9/24/2024	Mon, 9/9/2024
Sheet3

Cell Formulas
Range	Formula
G4:G7	G4	=WORKDAY($E4,$F4,VSTACK($D$2:$D$3,TOCOL($A$2:$C$32,,TRUE)))
H4:H7	H4	=WORKDAY(WORKDAY.INTL($E4,$F4,"0000000",VSTACK(TOCOL($A$2:$C$32,,TRUE)))-1,1,$D$2:$D$3)

 

[Tetra201]

The following simpler formula returns the expected results for the posted dataset:

=WORKDAY(WORKDAY.INTL(E4,F4,"0000000",$A$2:$C$32)-1,1,$D$2:$D$3)

 

[KRice]

Thanks @Tetra201...I didn't realize the "holidays" option would accept a multi-column range reference (I should have tried that).

 

[panostis]

I see. Just had the opportunity to put the formula to work and it does work for the great majority of scenarios. There was actually a scenario where if the calculation ended on a weekend and then the following Monday was a normal holiday (counted but go to next day), the end day would skip to Tuesday even though that Tuesday was in the column of dates to be excluded completely from calculations and would stay there not forwarding to Wednesday as it should. I found that this is something very rare and unlikely to come across in real life scenarios, but the way to fix it is to expand the "holidays" parameter of the workday function so that it doesn't only include the column of normal holidays but rather all columns (ie columns of dates to be completely excluded when counting as well as column of normal holidays). So the formula above would work even for that 1% of cases if changed to =WORKDAY(WORKDAY.INTL(E4,F4,"0000000",$A$2:$C$32)-1,1,$A$2:$D$3) . Not quite sure why that is though. I am sure you probably understand why this seems to work better... Be that as it may I would like to offer my gratitude and thank you very much for sharing a working solution. I guess simplicity is the secret to perfection. Much obliged for your time and effort sir(s). Problem solved!

 

[KRice]

You're right...good catch. After the first step to determine the initial landing spot (this counting process does count holidays but excludes A:C), we then go back one day and look for the next working day subject to whatever constraints you want. In this case, the next legitimate working day cannot land on any of the A:C dates or the holidays in D, so expanding the last argument to A:D will do that, but adjust the range of the last argument to cover all of the A:C dates and all of the D dates, so rather than A2:D3, it would be A2:D32 in this example case...

=WORKDAY(WORKDAY.INTL(E4,F4,"0000000",$A$2:$C$32)-1,1,$A$2:$D$32)