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
-
Paul Newcome ✭✭✭✭✭✭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.
-
Marvin Daniels ✭✭✭✭
Thanks Paul! That's much cleaner and worked like perfectly and now that I see what you've done, it makes complete sense.
-
Paul Newcome ✭✭✭✭✭✭
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)
Answers
-
Paul Newcome ✭✭✭✭✭✭
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.
-
Marvin Daniels ✭✭✭✭
Thanks Paul! That's much cleaner and worked like perfectly and now that I see what you've done, it makes complete sense.
-
Paul Newcome ✭✭✭✭✭✭
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)
-
Paul Newcome ✭✭✭✭✭✭
-
Marvin Daniels ✭✭✭✭
Thanks for clarifying Paul. I caught that, but forgot to mention it in my original response.
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T01:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T01:32:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
