Automate Formula Criterion
I'm sorry if the answer to my question is available. I could not find information about automating a scheduled update criterion included in a formula.
For a monthly dashboard, I created a series of formulas to count the number of topics at various development stages, total days in each stage, and the cycle time between each of the stages, such as from request date to publication date.
To avoid updating multiple formulas each month, is it possible to add a function to automate criterion to display the current month, the current month -1, or the required month based on information updated in one located each month?
Here is a formula example:
=COUNTIFS PubMonth:PubMonth, "4", PubYear:PubYear, "2018"
Thanks!
Comments
-
You can use MONTH(TODAY()) to get the current month. Add or subtract 1 as needed for prior or future month
If you plan on crossing the year boundary you will need to use the year as well
so
current period =COUNTIFS(PubMonth:PubMonth, MONTH(TODAY()), PubYear:PubYear, YEAR(TODAY()))previous period = IF(MONTH(TODAY()) > 1, COUNTIFS(PubMonth:PubMonth, MONTH(TODAY()) - 1, PubYear:PubYear, YEAR(TODAY())), COUNTIFS(PubMonth:PubMonth, 12, PubYear:PubYear, YEAR(TODAY()) - 1))
next period = IF(MONTH(TODAY()) < 12, COUNTIFS(PubMonth:PubMonth, MONTH(TODAY()) + 1, PubYear:PubYear, YEAR(TODAY())), COUNTIFS(PubMonth:PubMonth, 1, PubYear:PubYear, YEAR(TODAY()) + 1))
-
Perfect!
Thank you for solving the problem and including the formulas for all three scenarios.
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!