Find the Next non-Holiday Monday from a given date?

[IslayScotchFan]

Thought this would be a simple hurdle to clear but have been unable to do so. I have a series of dates created via the WORKDAY.INTL function with custom weekend & holiday parameters. For any date that does not fall on a working Monday, I need to roll that date forward to the next non-Holiday Monday. I can accomplish finding the next available Monday with a series of nested IF statements, but hard-coding a value to add to those results (based on the WEEKDAY value) undermines/ignores the holiday blackout days. Example:

Formula:
=IF(WEEKDAY(WORKDAY.INTL(E15,$C$7,"0000111",Table5[Date]),2)=1,WORKDAY.INTL(E15,$C$7,"0000111",Table5[Date]),IF(WEEKDAY(WORKDAY.INTL(E15,$C$7,"0000111",Table5[Date]),2)=2,WORKDAY.INTL(E15,$C$7,"0000111",Table5[Date])+6,IF(WEEKDAY(WORKDAY.INTL(E15,$C$7,"0000111",Table5[Date]),2)=3,WORKDAY.INTL(E15,$C$7,"0000111",Table5[Date])+5,IF(WEEKDAY(WORKDAY.INTL(E15,$C$7,"0000111",Table5[Date]),2)=4,WORKDAY.INTL(E15,$C$7,"0000111",Table5[Date])+4,"OOPS"))))

In the above, WORKDAY.INTL(E15,$C$7,"0000111",Table5[Date] results in a date of 12/28/2023 (a Thursday) which gets corrected via the IF statement to 1/1/2024 (which is a Monday, but obviously a holiday). The formula is bulky and doesn't return the desired result. I want to simplify the formula so if "=IF(WEEKDAY(WORKDAY.INTL(E15,$C$7,"0000111",Table5[Date]),2)=1,WORKDAY.INTL(E15,$C$7,"0000111",Table5[Date]) does not evaluate to a Monday the false statement would be "INDEX(Holiday!$B:$B,MATCH(WORKDAY.INTL(E15,$C$7,"0000111",Table5[Date]),Holiday!$A:$A,0)))". I have all of my available workdays in a list, but can't figure out how to make the non-Mondays roll forward to the next non-Holiday Monday.

Said all of that to say/ask: how do I convert any date to the next non-Holiday workday of my choosing (in this case a Monday) based on my blackout days?

Any suggestions?

 

[Fluff]

If you just want a Monday date, how about

=WORKDAY.INTL(E15,$C$7,"0111111",Table5[Date])

 

[IslayScotchFan]

I believe I would have to change the calculation for C7, otherwise it would give me every 8th Monday (example only). It could work, but there are several factors & calculations that influence the C7 reference.

 

[IslayScotchFan]

I believe I would have to change the calculation for C7, otherwise it would give me every 8th Monday (example only). It could work, but there are several factors & calculations that influence the C7 reference.

Disregard. That will work for my lookup table that I wanted to use in lieu of the chunky IF statements. Thank you so much for the help, didn't consider that approach!

 

[Fluff]

You're welcome & thanks for the feedback.