Round to Thursday if first Friday formula returns a holiday

cjtleno

New Member
Joined
Feb 9, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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))

1612888759970.png


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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
What happens if the Thursday is also a holiday, or won't that ever happen?
 

cjtleno

New Member
Joined
Feb 9, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 14, 2008
Messages
3,093
Office Version
  1. 365
Platform
  1. Windows
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))
 
Solution

cjtleno

New Member
Joined
Feb 9, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
Another option
Excel Formula:
=LET(Fri,WORKDAY.INTL(EOMONTH(A2,-1),1,"1111011"),IF(A2=0,"",Fri-(ISNUMBER(MATCH(Fri,E2:E14,0)))))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,104
Messages
5,622,746
Members
415,925
Latest member
Ryle23

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top