Formula for Highlighting Cell based on Parameters being met

Platform discussions
, ,
1

Hi All,

I am looking to understand the possible ways of achieving the below either through Automation, formulas or conditional formatting.

On My Monday board, I have a drop down column “Status” and a date column “date raised”. When the Status Column is marked as Not Start (default) and the date raised is over 30 days ago, I want the row to be highlighted red.

I’ve tried using automation, but I haven’t quite been able to achieve anything close using automation.

I’ve tried using conditional formatting alone, but this only allows me to use one condition to highlight the cell.

Therefore i’ve created a new column with a formula but i’ve only been able to get so far. IF{Status}=“Not Started"AND{Date Raised}=TODAY(”>30")TEXT(“Yes”)

Ideally if this element of the formula “IF{Status}=“Not Started"AND{Date Raised}=TODAY(”>30”)" was enough to then highlight the cell red that would be great. However, my thought was that if the conditions met, then it would enter Yes into the cell and using conditional formatting I could highlight the cell a colour based on the “Yes” in the cell.

Hope this makes sense and any guidance on where my formula is going wrong or a better way to implement this would be appreciated.

2

Hi Joe,

Try:

IF(AND({Status}="Not Started",DAYS(TODAY(), {Date})>30),"Yes","")

 

Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.

  • Create formulas without using the Formula column (and avoid its limitations)
  • Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
  • In one formula, update multiple columns from multiple items.

Check our blog for real use cases.

1 Like
3

Hello,
It sounds like you’re on the right track with using a formula column and then conditional formatting in Monday.com! Let’s break down where your formula might be going wrong and how to fix it, and then discuss the best way to implement the highlighting.

Where Your Formula is Going Wrong: TODAY(">30")

The main issue in your formula TODAY(">30") is that TODAY() returns the current date. You can’t directly compare it to a string like ">30" in that way to check for a past date.

To check if a date is more than 30 days ago, you need to:

  1. Get the current date.
  2. Subtract 30 days from the current date.
  3. Compare your “Date Raised” to that calculated date.

Monday com formulas use DAYS() and FORMAT_DATE() functions (or similar date manipulation functions) for these kinds of comparisons.

Corrected Formula for Your New Column

Here’s a more accurate formula you can use in your new column to achieve the “Yes” when conditions are met:

IF(AND({Status}="Not Started", DAYS(TODAY(), {Date Raised}) > 30), "Yes", "No")

Let’s break this down:

  • IF(condition, value_if_true, value_if_false): This is the basic structure of an IF statement.
  • AND(condition1, condition2): This checks if both condition1 and condition2 are true.
    • {Status}="Not Started": This checks if your “Status” column is exactly “Not Started”. Make sure the text matches exactly what’s in your dropdown.
    • DAYS(TODAY(), {Date Raised}) > 30: This is the crucial part for the date.
      • TODAY(): Gets the current date.
      • {Date Raised}: Refers to the value in your “Date Raised” column.
      • DAYS(date1, date2): This function calculates the number of days between date1 and date2. If date1 is later than date2, the result will be positive.
      • > 30: We are checking if the number of days between today and the date raised is greater than 30. This means the date raised was more than 30 days ago.

This formula will output “Yes” if both conditions are met, and “No” otherwise.

Implementing the Highlighting (Your Best Approach)

Your current thought process is the best way to achieve this in Monday com given the limitations of direct conditional formatting for multiple conditions on a single row highlight:

  1. Create a new “Helper” Formula Column: Use the formula provided above (e.g., call this column “Highlight Trigger”).
  2. Apply Conditional Formatting to the “Helper” Column:
    • Go to your board settings.
    • Select “Conditional Formatting”.
    • Add a new rule.
    • Condition: “If ‘Highlight Trigger’ is ‘Yes’”.
    • Action: “Highlight the entire row” (or specific cells if you prefer, but you want the row).
    • Color: Choose red.

This method works because Monday com’s conditional formatting can apply to the entire row based on a single cell’s value, which in your case, is the result of your multi-condition formula.

Why Direct Automation Might Not Work Easily

You mentioned trying automation. While Monday com’s automation is powerful, for something like “highlight row based on two dynamic conditions (status AND date calculation)”, it’s less straightforward. Automations are typically triggered by an event (e.g., “when status changes”) and then perform an action. Highlighting based on an ongoing time-based condition (date being older than 30 days) and another column’s value often requires a more complex setup or isn’t directly supported by standard highlighting automations. The formula + conditional formatting approach is more robust for this specific scenario.

Summary of Steps:

  1. Add a new “Formula” column to your Monday com board (e.g., name it “Highlight Trigger”).
  2. Paste the corrected formula into this new column:
    IF(AND({Status}="Not Started", DAYS(TODAY(), {Date Raised}) > 30), "Yes", "No")
  3. Set up Conditional Formatting:
    • Click on the column header of your “Highlight Trigger” column.
    • Select “Conditional Formatting”.
    • Click “Add new rule”.
    • Choose “If ‘Highlight Trigger’ is ‘Yes’”.
    • Under “Then highlight”, select “Entire row” and choose your desired red color.

This setup will achieve exactly what you’re looking for!
www smartsquarehmh com

1 Like