Forum Discussion
Solved
How to create rolling 12 month, and progress by 1 year
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 🙂
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))
5 Replies
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))
- Can you explain the numbers:
LET(FD,DATE(B3,A3,1),FD+CHOOSE(WEEKDAY(FD),16,15,14,20,19,18,27))
This way if I have to manipulate the day of the week. Instead of third tuesday, needs to be first I can adapt this formula.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
mathetes Perfect!!! Thanks
See the attached workbook.
