Next Monday unless holiday, then Tuesday

[sleepyowl86]

I'm needing a formula that, starting with today (whatever day that may be), returns the date of the following Monday unless it is a holiday, then Tuesday. It would save me so much time if I didn't have to change all the dates on a bunch of paperwork weekly.

 

[Momentman]

Do you have a List of holidays stored somewhere in the worksheet?

 

[sleepyowl86]

yes

 

[Momentman]

To get the next monday's date, you use a formula like A1+(7-WEEKDAY(A1,2)+1) and for the Next Tuesday, A1+(7-WEEKDAY(A1,2)+2)

So it all depends, you can VLOOKUP Monday's date on your list of holidays and if its is not found, i.e £N/A, then it is not a holiday, so you use the MONDAY formula to get your result or if it is found on your holiday list, then the formula for Tuesday is used instead

Without having sample data showing your structure, i cant say more

hope this helps

 

[Dave Patton]

Either of the following would work

Excel 2010
	A	B	C	D	E
1
2	Thu 12-Oct-17	Tue 17-Oct-17	Tue 17-Oct-17		16-Oct-17
3					11-Nov-17
4
6b

Cell Formulas
Range	Formula
B2	=WORKDAY.INTL(A2,1,"0111111")+ISNUMBER(MATCH(WORKDAY.INTL(A2,1,"0111111"),Holidays,0))
C2	=IF(WEEKDAY(A2,2)=1,A2,A2+8-WEEKDAY(A2,2))+ISNUMBER(MATCH(IF(WEEKDAY(A2,2)=1,A2,A2+8-WEEKDAY(A2,2)),Holidays,0))

Named Ranges
Name	Refers To	Cells
Holidays	='6b'!$E$2:$E$3

 

[Dave Patton]

=WORKDAY(A2+7-WEEKDAY(A2,2),1,Holidays)

 

[Momentman]

=WORKDAY(A2+7-WEEKDAY(A2,2),1,Holidays)

As long as Tuesday is not a public holiday too

 

[Dave Patton]

If Tuesday is a holiday, it shows Wednesday.

 

[Momentman]

If Tuesday is a holiday, it shows Wednesday.

Sure. Whether or not that's what the OP expects is a different matter altogether. Great formula nonetheless