Change status if date column is within xx months

Platform discussions
, ,
1

Hi there,

hope someone can help me with this:

We have a date column that’s called “Expected closing date” and a status column called “Priority”. What we want is the following 3 automations:

  1. If “Expected closing date” is within this quarter (or alternative: within the next 3 months), then set status column “Priority” to HIGH.
  2. If “Expected closing date” is within this year (or alternative: within the next 12 months), then set status column “Priority” to NORMAL.
  3. If “Expected closing date” is after this year (or alternative: after the next 12 months), then set status column “Priority” to LOW.

Does anybody have a solution for this?

Thanks!

2

Hi Manuela,

Unfortunately it’s not possible to use Date columns as conditions in that way. They really only work as triggers.

You have a few options here:

  1. Create a bunch of monday.com automations such as “3 months before [date] arrives, set Priority to High” (and then add “2 months before…”, “1 month before…” etc.). Then do the same for the other categories and everything from 1 to 12 months, with Low being the default. This isn’t ideal, because it won’t change the priority right away. However, if you’re confident of setting the correct priority initially, that shouldn’t be a problem. In that case, you wouldn’t even need to add automations for 1-12 months—you could just add them for your thresholds.
  2. Use a formula column. Really if your priority column is just a different way of displaying data from your date column, the ideal solution is not to store separate data. So you could use a formula such as (simple example that uses day counts 0-89 days = High, 90-364 days = Medium, >365 days = Low):
IF(
	FORMAT_DATE({Expected closing date},"YYYYMMDD")<FORMAT_DATE(ADD_DAYS(TODAY(),90),"YYYYMMDD"),
    "High",
    IF(
	    FORMAT_DATE({Expected closing date},"YYYYMMDD")<FORMAT_DATE(ADD_DAYS(TODAY(),365),"YYYYMMDD"),
        "Medium",
        "Low"
    )
)
  1. Use an app or external integration. You could use General Caster to achieve this really easily for instance so that it would set automatically when you input the date—then monday.com automations could take care of the rest.

Hope that helps—I think the formula would be the best choice. You can also use conditional formatting to add color coding to the formula cell, which will really add to the look and feel.

3

Hi Francis,

thank you so much! For now, the formula is definitely the best solution for our problem and I’ve already successfully set it up in our board.

Hope monday can implement using date columns as conditions at some point :slight_smile:

all the best,
Manuela

1 Like