Change status if date column is within xx months

Platform discussions
, ,
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.