# Round to Thursday if first Friday formula returns a holiday

#### cjtleno

##### New Member
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.

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### Fluff

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

#### cjtleno

##### New Member
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

##### Well-known Member
Book1
ABCDE
1Date1st FridayHolidays
22/16/20212/4/202112/25/2020
31/1/2021
41/18/2021
52/5/2021
63/2/2021
75/31/2021
87/5/2021
98/16/2021
109/6/2021
1111/11/20201
1211/25/2021
1311/26/2021
1412/24/2021
Sheet5
Cell Formulas
RangeFormula
C2C2=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

##### New Member
Book1
ABCDE
1Date1st FridayHolidays
22/16/20212/4/202112/25/2020
31/1/2021
41/18/2021
52/5/2021
63/2/2021
75/31/2021
87/5/2021
98/16/2021
109/6/2021
1111/11/20201
1211/25/2021
1311/26/2021
1412/24/2021
Sheet5
Cell Formulas
RangeFormula
C2C2=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

##### MrExcel MVP, Moderator
Another option
Excel Formula:
``=LET(Fri,WORKDAY.INTL(EOMONTH(A2,-1),1,"1111011"),IF(A2=0,"",Fri-(ISNUMBER(MATCH(Fri,E2:E14,0)))))``

Replies
5
Views
141
Replies
1
Views
103
Replies
3
Views
534
Replies
5
Views
356
Replies
5
Views
222

1,127,098
Messages
5,622,677
Members
415,921
Latest member
ExcelNoob28

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back