Formula Column

Platform discussions
,
1

I am using a column named Planned Timeline* to select dates and a status column named Status* with labels “Planned Task,” “Working on it,” “Completed,” and “Stuck / On Hold” in monday.com. I want to create a formula column named Priority with the following conditions:

For Example If the Planned Timeline* is between 01-Oct-24 and 30-Oct-24, and the end date (30-Oct-24) is within 7 days of the current date, the priority should be set to “High” with a red icon, only if the Status* is not “Completed.”

If the Planned Timeline* dates are not set (i.e., empty), the priority should automatically be “Medium” with a blue icon, regardless of the Status*.

2

Hi Shaoaib,

Shanine here from Mindflows :slight_smile:

I suggest using Make.com/Zapier for this scenario.

3

@Shoaibali450

Try this:

IF({Status} <> "Complete",
  IF(
    AND(
      DAYS(FORMAT_DATE({Timeline#Start}),"10/1/2024") >= 0, 
      DAYS("10/30/2024",FORMAT_DATE({Timeline#Start})) >=0,
      ABS(DAYS(FORMAT_DATE({Timeline#End}),
         FORMAT_DATE(TODAY()))) <= 7
    ),
    "🟥 High",
    IF({Timeline#Start} = "", "🟦 Medium", "")
    , ""
  )
)

Jim - The Monday Man
Get Custom Apps, Integrations & Automations for monday

5

This is Great help. However can we remove the dates from this formula? the above given dates are only for example. We need to select the timeline column instead to pick date 7 days before timeline ends.
Looking forward to get this done.

6

I got it now. Here you go:

IF({Status} = "Completed", "", 
  IF(DAYS(FORMAT_DATE({Timeline#End}),
    FORMAT_DATE(TODAY())) <= 7, "🟥 High",
    "🟦 Medium"
  )
)

Jim - The Monday Man
Get Custom Apps, Integrations & Automations for monday