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, month-9 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 M-1 and ended on M-11 on 11, then entered 0 on M-12. This is assuming you will always want M-12 to list the current month.
Here is the formula I put in row 2:
=IF((MONTH(TODAY()) + [M-1]1) < 13, MONTH(TODAY()) + [M-1]1, MONTH(TODAY()) + [M-1]1 - 12)
And the formula I put in row 3:
=IF((OR((MONTH(TODAY()) + [M-1]1) > 12, [M-1]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 follow-up questions:
-Am I right in understanding that your year begins in April and ends in March?
-When M-12 is over and M-1 begins, do you want the M-2 through M-12 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 M-1 and ended on M-11 on 11, then entered 0 on M-12. This is assuming you will always want M-12 to list the current month.
Here is the formula I put in row 2:
=IF((MONTH(TODAY()) + [M-1]1) < 13, MONTH(TODAY()) + [M-1]1, MONTH(TODAY()) + [M-1]1 - 12)
And the formula I put in row 3:
=IF((OR((MONTH(TODAY()) + [M-1]1) > 12, [M-1]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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!