Forum Discussion
How to create rolling 12 month, and progress by 1 year
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))
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))
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
