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

jlmre00

New Member
Joined
Aug 19, 2024
Messages
12
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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!
 

Attachments

  • 123.png
    123.png
    61.9 KB · Views: 9

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hi & welcome to MrExcel.
How about
Excel Formula:
=IF(WORKDAY.INTL(EOMONTH(CG$8,0)+1,-1,"1110111")=CG$8,0.39,0)
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,374
Messages
6,171,727
Members
452,419
Latest member
mapa

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
Back
Top