Round to Thursday if first Friday formula returns a holiday

[cjtleno]

Hello,

I have a formula that returns the first Friday of the month based on the date entered into another cell. I can't figure out how to have the date "round" to the Thursday before if the 1st Friday is a holiday.

The formula for the 1st Friday calculation is:

=IF(A2=0," ",(CEILING(EOMONTH(A2,-1)-5,7)+6))

What I am looking to do is have the formula find the 1st Friday of the month and then see if that date matches the date in the "Holiday's" column and if it does, return the Thursday date before the holiday. Hope I am making sense and thank you for any help that you can give.

 

[Fluff]

Hi & welcome to MrExcel.
What happens if the Thursday is also a holiday, or won't that ever happen?

 

[cjtleno]

Hi & welcome to MrExcel.
What happens if the Thursday is also a holiday, or won't that ever happen?

Hi and Thank you,

I don't believe that will ever happen (or at least I am not worrying about that until it comes up).

 

[lrobbo314]

Book1
	A	B	C	D	E
1	Date		1st Friday		Holidays
2	2/16/2021		2/4/2021		12/25/2020
3					1/1/2021
4					1/18/2021
5					2/5/2021
6					3/2/2021
7					5/31/2021
8					7/5/2021
9					8/16/2021
10					9/6/2021
11					11/11/20201
12					11/25/2021
13					11/26/2021
14					12/24/2021
Sheet5

Cell Formulas
Range	Formula
C2	C2	=LET(FF,IF(A2=0," ",(CEILING(EOMONTH(A2,-1)-5,7)+6)),HH,E2:E14,IF(ISNUMBER(MATCH(FF,HH,0)),FF-1,FF))

 

[cjtleno]

Book1
	A	B	C	D	E
1	Date		1st Friday		Holidays
2	2/16/2021		2/4/2021		12/25/2020
3					1/1/2021
4					1/18/2021
5					2/5/2021
6					3/2/2021
7					5/31/2021
8					7/5/2021
9					8/16/2021
10					9/6/2021
11					11/11/20201
12					11/25/2021
13					11/26/2021
14					12/24/2021
Sheet5

Cell Formulas
Range	Formula
C2	C2	=LET(FF,IF(A2=0," ",(CEILING(EOMONTH(A2,-1)-5,7)+6)),HH,E2:E14,IF(ISNUMBER(MATCH(FF,HH,0)),FF-1,FF))

Thank you. That worked perfectly.

 

[Fluff]

Another option

=LET(Fri,WORKDAY.INTL(EOMONTH(A2,-1),1,"1111011"),IF(A2=0,"",Fri-(ISNUMBER(MATCH(Fri,E2:E14,0)))))