[Solved] Formula to identify if a date is 2nd or 4th Thursday

[jstack]

I'm having trouble finding a formula. I need to return a specific result if A1 = the 2nd or 4th Thursday of any given month

[MrProgrammer]

I need to return a specific result if A1 = the 2nd or 4th Thursday of any given month

Get the date of the first Thursday for the month and subtract that to see if the result is 7 (2nd) or 21 (4th).
[Tutorial] Calc date formulas, section P


If you need any additional assistance attach a document demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[jstack]

I'm not sure that does what I need. If A1 = 8/25, and that happens to be the 2nd or 4th Thursday, then I need D25 = YES, otherwise 0. If A1 equals any other date than the 2nd or 4th Thursday, D25 will equal 0. Thanks in advance

[Villeroy]

Column A: random dates
Column B: first tuesday
Column C: test if the difference between A and B is either 7 or 21

[Lupp]

Sorry something like "8/25" isn't a date at all. There is no date without a year, and crippled dates you don't know a year for can't get assigned a weekday. Let me assume, your sloppy 8/25 was meant to mean 2022-08-25.
You will see that the formula I suggest below will return the ordinal number 4 - and actually the days number 4, 11, 18, and 25 of that august are thursdays.

I'm having trouble finding a formula. I need to return a specific result if A1 = the 2nd or 4th Thursday of any given month.

Let me understand this as.
Suppose A1 contains a date. I want to know if this date is the second or the fourth thursday of the month to which that date belongs.

I wouldn't insist on finding the first thursday of that month, but try to find two subexpressions:
One deciding if the day is a thursday at all - and
One giving the the number of times the weekday of that date was reeoccurring in this month already.
Assuming the first expression returns 0 for "not a thursday" and 1 for "yes, a tuesday", you only need to multiply the first reult with the second to get a kind of ordinal number for "thursday": 0 for "not a thursday" and 1, 2, 3, 4, or 5 as the ordinal if the date is a tuesday.
First expression: (WEEKDAY(A1;2)=4) [where 4 is the number telling "thursday".
That's 'Boolean', but FALSE can also represent the number 0 and TRUE the number 1.]
Second expression (INT((DAY(A1)-1)/7)+1) by which you count how often the current weekday already occurred in the month.
If (WEEKDAY(A1;2)=4) * (INT((DAY(A1)-1)/7)+1) returns 2 or 4, you have one of your special cases. You can now just check if

Code: Select all

(ABS((WEEKDAY(A1;2)=4) * (INT((DAY(A1)-1)/7)+1) - 3)=1

or you can assign different values or formula results to both the cases by

Code: Select all

=CHOOSE((WEEKDAY(A1;2)=4) * (INT((DAY(A1)-1)/7)+1) + 1; 0; 0;, IsSecondThuFormula; 0; IsFourthThuFormula; 0)

which will return 0 if the date from A1 either is not a tuesday at all or the first, third, or fifth one in the month. (The addition of + 1 is necessary because CHOOSE numbers its positions 1-based.)
This solution should also be flexible enough to be easily adaptable to different but similar cases.