Adding calendar days but result needs to be preceding business day

[aliecat08]

I need to calculate various deadlines that either add or subtract calendar days to a specific date in a cell, but the result must be the first preceding business day if it lands on a weekend or holiday (I have a separate sheet in same workbook that lists all the holidays through 2026)

For example, if I need to add 30 calendar days to 11/24/2023, it'll be 12/24/2023, which is a holiday so the result should be 12/22/2023.

Any help is much appreciated!!!

 

[Dave Patton]

I used Let which you may not have.

Just expand the formula so that you do not need to use Let.

try

Workdays.xlsm
	A	B	C	D	E	F
1						Holidays
2		24-Nov-23	30	Fri 22-Dec-23		24-Dec-23
3
5a

Cell Formulas
Range	Formula
D2	D2	=LET(d,WORKDAY.INTL(B2,30,"0000000",Holidays),IF(d>B2+30,WORKDAY.INTL(d,-1,1,Holidays)))

Named Ranges
Name	Refers To	Cells
'5a'!Holidays	='5a'!$F$2	D2

or

Workdays.xlsm
	A	B	C	D	E	F
1						Holidays
2		24-Nov-23	30	Fri 22-Dec-23		24-Dec-23
3
5a

Cell Formulas
Range	Formula
D2	D2	=LET(d,WORKDAY.INTL(B2,30,"0000000",Holidays),IF(d>B2+30,WORKDAY.INTL(d,-1,"1111011",Holidays)))

Named Ranges
Name	Refers To	Cells
'5a'!Holidays	='5a'!$F$2	D2

 

[aliecat08]

Just expand the formula so that you do not need to use Let.

I do not have let and am very confused by what you mean in regards to this

 

[Dave Patton]

Workdays.xlsm
	A	B	C	D	E	F
1						Holidays
2		24-Nov-23	30	Fri 22-Dec-23		24-Dec-23
3		24-Nov-23	30	24-Dec-23	Fri 22-Dec-23
4		24-Nov-23	30		Fri 22-Dec-23
5a

Cell Formulas
Range	Formula
D2	D2	=LET(d,WORKDAY.INTL(B2,30,"0000000",Holidays),IF(d>B2+30,WORKDAY.INTL(d,-1,1,Holidays)))
D3	D3	=B3+C3
E3	E3	=IF(D3=Holidays,WORKDAY.INTL(D3,-1,1,I2))
E4	E4	=IF(B4+C4=Holidays,WORKDAY.INTL(B4+C4,-1,1,Holidays))

Named Ranges
Name	Refers To	Cells
'5a'!Holidays	='5a'!$F$2	D2, E3:E4

 

[Dave Patton]

Workdays.xlsm
	B	C	D
5	24-Nov-23	30	Fri 22-Dec-23
6
5a

Cell Formulas
Range	Formula
D5	D5	=WORKDAY(B5+C5+1,-1,Holidays)

Named Ranges
Name	Refers To	Cells
'5a'!Holidays	='5a'!$F$2	D5

 

[aliecat08]

Workdays.xlsm
	B	C	D
5	24-Nov-23	30	Fri 22-Dec-23
6
5a

Cell Formulas
Range	Formula
D5	D5	=WORKDAY(B5+C5+1,-1,Holidays)

Named Ranges
Name	Refers To	Cells
'5a'!Holidays	='5a'!$F$2	D5

YESSS!! This is it.

I have a question though, just so I understand the concept behind the formula, what do the +1 , and -1, do?