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
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))