Daily 3yr CF: Need to flag 4th/last Thursday of each month

[jlmre00]

Hi all!

HELP: I need to flag 4th Thursday of month - say IF it's 4th Thursday (or 5th Thursday for 31 day months) of that month return "1"

BACKGROUND: working on a daily 3 year cash flow, where there is a payment on the last Thursday of each month

PROBLEM: some months have 31 days e.g. in March 2023 two flags appear Thursday March 23 and Thursday March 30th (see screen shot)

1. I tried Date/WEEKDAY and tried Calculating Week of Month (line 3 in screenshot) in a separate line as a supporting calculation (which comes out correct), but some months have 5th week like March 2023

2. Line 24 and 25 are my test lines where I am trying to get 4th Thursday of each month to flag with "1" or 39%

Line 24: =IF(AND(CG2="Thu",OR(MAX(CG3:CP3=4,CG3:CP3=5))),1,0)

Line 25: =IF(CG8=DATE(YEAR(CG8),MONTH(CG8),1+4*7)-WEEKDAY(DATE(YEAR(CG8),MONTH(CG8),8-5)),"39.0%","0.0%")

Thank you in advance!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(WORKDAY.INTL(EOMONTH(CG$8,0)+1,-1,"1110111")=CG$8,0.39,0)

 

[jlmre00]

Hi & welcome to MrExcel.
How about

=IF(WORKDAY.INTL(EOMONTH(CG$8,0)+1,-1,"1110111")=CG$8,0.39,0)

That worked great! Thank you very much!!

 

[Fluff]

You're welcome & thanks for the feedback.