How to create dynamic rolling month and year?
I am trying to create a table to calculate score from another sheet on a 12 months rolling basis, this data is to be published on the dashboard.
It's easy to have the 12th month data which is current month.
However this formula doesn't work across the board as shown above, month9 would be 0.
How can I have a formula to capture the rolling month and year so that I can then sum or average the score from the score sheet base on these rolling month and year?
Appreciate assistance. Thanks.
Best Answer

Vivien,
I think I may have come up with something. See screenshot below.
I added a helper row (row 1) that started with the number 1 on M1 and ended on M11 on 11, then entered 0 on M12. This is assuming you will always want M12 to list the current month.
Here is the formula I put in row 2:
=IF((MONTH(TODAY()) + [M1]1) < 13, MONTH(TODAY()) + [M1]1, MONTH(TODAY()) + [M1]1  12)
And the formula I put in row 3:
=IF((OR((MONTH(TODAY()) + [M1]1) > 12, [M1]1 = 0)), YEAR(TODAY()), YEAR(TODAY())  1)
Let me know if this works for you!
Best,
Heather
Answers

Additional question, since I am using TODAY function in this metric sheet to cross reference the source sheet, will it be updated automatically without opening this metric sheet?

Hi @Vivien Chong ,
The TODAY function updates automatically, so if you are using the metrics in a dashboard, it will update automatically. A few followup questions:
Am I right in understanding that your year begins in April and ends in March?
When M12 is over and M1 begins, do you want the M2 through M12 columns to be blank, or do you want them to still show the previous year's data?
Thanks,
Heather

@Heather D the formula should be auto calculating the constant rolling.
If this month is March, it should be showing: April to March
If this month is April, it should be showing: May to April
With these constant rolling dynamic month and year, I can then capture my data base on 12 months rolling constantly.

Vivien,
I think I may have come up with something. See screenshot below.
I added a helper row (row 1) that started with the number 1 on M1 and ended on M11 on 11, then entered 0 on M12. This is assuming you will always want M12 to list the current month.
Here is the formula I put in row 2:
=IF((MONTH(TODAY()) + [M1]1) < 13, MONTH(TODAY()) + [M1]1, MONTH(TODAY()) + [M1]1  12)
And the formula I put in row 3:
=IF((OR((MONTH(TODAY()) + [M1]1) > 12, [M1]1 = 0)), YEAR(TODAY()), YEAR(TODAY())  1)
Let me know if this works for you!
Best,
Heather

Thanks @Heather D , will test it out.
Help Article Resources
Categories
Check out the Formula Handbook template!