Rolling 12-Month Grid
I've built the rolling 12-month grid below, which will be referenced elsewhere in this summary sheet.It works, but this could be cleaner. I know I could remove the "helper" row (row 1), but this is how I worked it out, so I kept it for now. Any suggestions for improvements would be appreciated.
In m1 row 2, I have this formula (columns m2-12 have the same, but the row 1 reference changes):
=IF(MONTH(TODAY()) - [m1]1 = 0, 12, IF(MONTH(TODAY()) - [m1]1 = -1, 11, IF(MONTH(TODAY()) - [m1]1 = -2, 10, IF(MONTH(TODAY()) - [m1]1 = -3, 9, IF(MONTH(TODAY()) - [m1]1 = -4, 8, IF(MONTH(TODAY()) - [m1]1 = -5, 7, IF(MONTH(TODAY()) - [m1]1 = -6, 6, IF(MONTH(TODAY()) - [m1]1 = -7, 5, IF(MONTH(TODAY()) - [m1]1 = -8, 4, IF(MONTH(TODAY()) - [m1]1 = -9, 3, IF(MONTH(TODAY()) - [m1]1 = -10, 2, IF(MONTH(TODAY()) - [m1]1 = -11, 1, MONTH(TODAY()) - [m1]1))))))))))))
In m1 row 3, I have this formula:
=IF(MONTH(TODAY()) - [m1]1 = 0, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -1, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -2, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -3, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -4, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -5, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -6, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -7, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -8, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -9, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -10, YEAR(TODAY()) - 1, IF(MONTH(TODAY()) - [m1]1 = -11, YEAR(TODAY()) - 1, YEAR(TODAY())))))))))))))
Best Answers
-
For the Month row you would use:
=IF(=IF(MONTH(TODAY()) - [m1]$1 < 1, MONTH(TODAY()) + (12 - [m1]$1), MONTH(TODAY()) - [m1]$1)
And for the Year row:
=IF(MONTH(TODAY()) - [m1]$1 < 1, YEAR(TODAY()) - 1, YEAR(TODAY()))
Put those into the [m1] column and you should be able to dragfill to the right.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks Paul! That's much cleaner and worked like perfectly and now that I see what you've done, it makes complete sense.
-
For everyone else... It looks like I got a little ahead of myself with the formula for the Month row and threw in an extra "=IF(" at the beginning. That formula will not work as it is above. You will need to adjust it accordingly.
=IF(MONTH(TODAY()) - [m1]$1 < 1, MONTH(TODAY()) + (12 - [m1]$1), MONTH(TODAY()) - [m1]$1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
For the Month row you would use:
=IF(=IF(MONTH(TODAY()) - [m1]$1 < 1, MONTH(TODAY()) + (12 - [m1]$1), MONTH(TODAY()) - [m1]$1)
And for the Year row:
=IF(MONTH(TODAY()) - [m1]$1 < 1, YEAR(TODAY()) - 1, YEAR(TODAY()))
Put those into the [m1] column and you should be able to dragfill to the right.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks Paul! That's much cleaner and worked like perfectly and now that I see what you've done, it makes complete sense.
-
For everyone else... It looks like I got a little ahead of myself with the formula for the Month row and threw in an extra "=IF(" at the beginning. That formula will not work as it is above. You will need to adjust it accordingly.
=IF(MONTH(TODAY()) - [m1]$1 < 1, MONTH(TODAY()) + (12 - [m1]$1), MONTH(TODAY()) - [m1]$1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Marvin Daniels Happy to help! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks for clarifying Paul. I caught that, but forgot to mention it in my original response.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!