Feb 26 2024 10:42 AM - edited Feb 26 2024 10:43 AM
Ok, so what i have so far. we have meetings every third tuesday (ignore the screenshot) of the month. I used in C:
=DATE(B2,A2,1+7*3)-WEEKDAY(DATE(B2,A2,8-3))
which is doing exactly what I need. But im planning for the future, how do I get my Column A to rotate after 12 back to 1 AND the Year to add 1 when the month rotates? When I leave this job I still want this app to work :)
Feb 26 2024 12:08 PM
See the attached workbook.
Feb 26 2024 12:23 PM
Solution
I have given you a different formula for your column C as well, but you'll have to have a current version of Excel, or a subscription to Microsoft 365 for it to work, because it takes advantage of the LET function.
That aside, here are the formulas in columns A, B, and C in the attached example spreadsheet.
A: =IF(A2=12,1,A2+1)
B: =IF(A2=12,B2+1,B2)
C: =LET(FD,DATE(B3,A3,1),FD+CHOOSE(WEEKDAY(FD),16,15,14,20,19,18,27))
Feb 26 2024 01:10 PM
@mathetes Perfect!!! Thanks
May 07 2024 01:13 PM
May 07 2024 01:26 PM
FD is the first day of the month. The numbers 16,15,14,20,19,18,17 specify how many days you have to add to this to end up on the 3rd Tuesday. For example, if the first day is a Sunday WEEKDAY(FD)=1, and the 3rd Tuesday will be the 17th, so you have to add 16 days.
If you want the 1st Tuesday, you have to add 14 days less, so the numbers in the formula will be
2,1,0,6,5,4,3
Feb 26 2024 12:23 PM
Solution
I have given you a different formula for your column C as well, but you'll have to have a current version of Excel, or a subscription to Microsoft 365 for it to work, because it takes advantage of the LET function.
That aside, here are the formulas in columns A, B, and C in the attached example spreadsheet.
A: =IF(A2=12,1,A2+1)
B: =IF(A2=12,B2+1,B2)
C: =LET(FD,DATE(B3,A3,1),FD+CHOOSE(WEEKDAY(FD),16,15,14,20,19,18,27))