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!

Find more posts tagged with

Hospitality

Formulas

Support

Comments

rjudenberg

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))

Ginger Mason

Perfect!

Thank you for solving the problem and including the formulas for all three scenarios.

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.