Unexpected flaw in WORKDAY formula to calculate date

[EdNerd]

We work with data reports. Depending on the date the data is generated and the intended routing of the report, each report has milestone dates as it runs through the editing and QA processes. I created a spreadsheet which we all use to calculate our due dates at these various milestones. It's been working fine - until someone did something I never expected them to do!!

The formula in question is (for row 47):
=IF(ISERR(MATCH($F47,Class,0)),0,IF($D47>0,WORKDAY($D47,HLOOKUP($F47,WhenDue,2,FALSE),Holidays),""))

The start date is in D47
I use three named ranges of values to assist in calculating the milestone due dates:
-- Class gives us the intended routing: This value is in F47
-- WhenDue gives us the number of days to the milestone
-- Holidays is a list of dates that are removed from the date calculations for various company reasons. For instance, if there's a training day, we're not expected to move the report forward on that day, so we get a "freebie". If the result occurs on a Monday, and the normal routing would be due on Thursday, but we have a "freebie" on Tuesday, then routing is pushed back to Friday.

And here's where the "unexpected flaw" comes in. The formula works great -- AND THEN ....
We had data generated on a "freebie" day. The expectation was that, because this specific date is included in the Holidays range listing, it would be ignored and the due date pushed back. To use the example above:
If the result occurred on Monday, with a "freebie" on Tuesday, routing shows as Friday as expected.
If the result occurred on the Tuesday freebie day, we would ~~expect~~ the routing milestone to push to Monday (since WORKDAY excludes weekends).
Instead, we see the milestone on Friday.

So it seems like I need a way to detect if the date in D7 is included in the Holidays table, and if so increment the date until it's valid. Or something like that?? What would be the easiest way to adjust this to give the result we need?

 

[KRice]

I don't see the problem. WORKDAY does not "count" the start date. A Monday start date in D47 with a freebie day the next day (Tue), and let's say a 3-day due date, would give an action period of Wed, Thu, Fri...so the due date would be Fri. Similarly, a Tuesday start date in D47 (which happens to fall on the freebie date) still doesn't count...that's day 0...so a 3-day due date would still give an action period of Wed, Thu, Fri...the due date is still Friday. The start date would need to be Wed in order for a 3-day action period to jump to the following Monday. Am I missing something?

 

[EdNerd]

I don't see the problem. WORKDAY does not "count" the start date. A Monday start date in D47 with a freebie day the next day (Tue), and let's say a 3-day due date, would give an action period of Wed, Thu, Fri...so the due date would be Fri. Similarly, a Tuesday start date in D47 (which happens to fall on the freebie date) still doesn't count...that's day 0...so a 3-day due date would still give an action period of Wed, Thu, Fri...the due date is still Friday. The start date would need to be Wed in order for a 3-day action period to jump to the following Monday. Am I missing something?

That is all correct. The issue is not with WORKDAY. The problem is in my entire formula, which fails to account for a start date on a freebie day. I'm wondering if there's any good way to get around this. Perhaps I need a macro to check the start date: if it exists in the Holidays range, then increment the date until it isn't?? Or would it be easier to adjust the formula??

 

[KRice]

I see now...thank you. Yes, how 'bout using:

=WORKDAY(C47-1,1,Holidays)

where C47 is the "data generated" date you mentioned. We go back 1 day before and then look for the next actual workday while recognizing Holidays and weekends...so we'll jump forward to the original day if it's not a holiday or weekend day...or jump forward to the next actual workday.

 

[KRice]

...like this:

MrExcel_20240224.xlsx
	A		C	D	E	F	G	H	I	J	K	L
45	2/26/2024										#days to MS
46	Ref Dates		"Data generated" Date	Start Date	Due Date	Class		Holidays	Class	WhenDue	3
47	Mon, 2/26/24		Mon, 2/26/24	Mon, 2/26/24	Fri, 3/1/24	C		Tue, 2/27/24	A	A	B	C
48	Tue, 2/27/24		Tue, 2/27/24	Wed, 2/28/24	Mon, 3/4/24	C			B	1	2	3
49	Wed, 2/28/24		Wed, 2/28/24	Wed, 2/28/24	Mon, 3/4/24	C			C
50	Thu, 2/29/24		Thu, 2/29/24	Thu, 2/29/24	Tue, 3/5/24	C
51	Fri, 3/1/24		Fri, 3/1/24	Fri, 3/1/24	Wed, 3/6/24	C
52	Sat, 3/2/24		Sat, 3/2/24	Mon, 3/4/24	Thu, 3/7/24	C
53	Sun, 3/3/24		Sun, 3/3/24	Mon, 3/4/24	Thu, 3/7/24	C
54	Mon, 3/4/24		Mon, 3/4/24	Mon, 3/4/24	Thu, 3/7/24	C
55	Tue, 3/5/24		Tue, 3/5/24	Tue, 3/5/24	Fri, 3/8/24	C
56	Wed, 3/6/24
57	Thu, 3/7/24
58	Fri, 3/8/24
59	Sat, 3/9/24
60	Sun, 3/10/24
Sheet6

Cell Formulas
Range	Formula
K46	K46	=HLOOKUP($F47,WhenDue,2,FALSE)
A47:A60	A47	=$A$45+SEQUENCE(14,,0)
D47:D55	D47	=WORKDAY(C47-1,1,Holidays)
E47:E55	E47	=IF(ISERR(MATCH($F47,Class,0)),0,IF($D47>0,WORKDAY($D47,HLOOKUP($F47,WhenDue,2,FALSE),Holidays),""))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Class	=Sheet6!$I$47:$I$49	E47:E55
Holidays	=Sheet6!$H$47:$H$53	D47:E55
WhenDue	=Sheet6!$J$47:$L$48	K46, E47:E55

You can incorporate the formula for "jumping" to the first available workday directly into your original formula if desired, rather than breaking it out as a separate formula like my example.

 

[KRice]

Incorporated into your formula and a slight revision to trap errors differently, see the purple cells:

MrExcel_20240224.xlsx
	A		C	D	E	F	G	H	I	J	K	L
45	2/26/2024										#days to MS
46	Ref Dates		"Data generated" Date	Start Date	Due Date	Class		Holidays	Class	WhenDue	3
47	Mon, 2/26/24		Mon, 2/26/24	Mon, 2/26/24	Fri, 3/1/24	C		Tue, 2/27/24	A	A	B	C
48	Tue, 2/27/24		Tue, 2/27/24	Wed, 2/28/24	Mon, 3/4/24	C			B	1	2	3
49	Wed, 2/28/24		Wed, 2/28/24	Wed, 2/28/24	Mon, 3/4/24	C			C
50	Thu, 2/29/24		Thu, 2/29/24	Thu, 2/29/24	Tue, 3/5/24	C
51	Fri, 3/1/24		Fri, 3/1/24	Fri, 3/1/24	Wed, 3/6/24	C
52	Sat, 3/2/24		Sat, 3/2/24	Mon, 3/4/24	Thu, 3/7/24	C
53	Sun, 3/3/24		Sun, 3/3/24	Mon, 3/4/24	Thu, 3/7/24	C
54	Mon, 3/4/24		Mon, 3/4/24	Mon, 3/4/24	Thu, 3/7/24	C
55	Tue, 3/5/24		Tue, 3/5/24	Tue, 3/5/24	Fri, 3/8/24	C
56	Wed, 3/6/24
57	Thu, 3/7/24			Mon, 2/26/24	Fri, 3/1/24	C
58	Fri, 3/8/24			Tue, 2/27/24	Mon, 3/4/24	C
59	Sat, 3/9/24			Wed, 2/28/24	Mon, 3/4/24	C
60	Sun, 3/10/24			Thu, 2/29/24	Tue, 3/5/24	C
Sheet6

Cell Formulas
Range	Formula
K46	K46	=HLOOKUP($F47,WhenDue,2,FALSE)
A47:A60	A47	=$A$45+SEQUENCE(14,,0)
D47:D55	D47	=WORKDAY(C47-1,1,Holidays)
E47:E55	E47	=IF(ISERR(MATCH($F47,Class,0)),0,IF($D47>0,WORKDAY($D47,HLOOKUP($F47,WhenDue,2,FALSE),Holidays),""))
E57:E60	E57	=IF($D57>0,IFERROR(WORKDAY(WORKDAY($D57-1,1,Holidays),HLOOKUP($F57,WhenDue,2,FALSE),Holidays),""),"")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Class	=Sheet6!$I$47:$I$49	E47:E55
Holidays	=Sheet6!$H$47:$H$53	D47:E55, E57:E60
WhenDue	=Sheet6!$J$47:$L$48	K46, E47:E55, E57:E60

 

[EdNerd]

Sorry for the delay in response. Your answer worked perfect!! Thank you so much for all your help!

 

[KRice]

Thanks for the update...that is good news. I'm happy to help.