SOLVED

# How to create rolling 12 month, and progress by 1 year

Copper Contributor

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

5 Replies

# Re: How to create rolling 12 month, and progress by 1 year

See the attached workbook.

best response confirmed by JustStartingFormula (Copper Contributor)
Solution

# Re: 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))

# Re: How to create rolling 12 month, and progress by 1 year

@mathetes Perfect!!! Thanks

# Re: How to create rolling 12 month, and progress by 1 year

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.

# Re: How to create rolling 12 month, and progress by 1 year

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

1 best response

Accepted Solutions
best response confirmed by JustStartingFormula (Copper Contributor)
Solution

# Re: 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))