How to highlight the pending tasks depending on the urgency?

1

So I’m trying to get the delayed tasks highlighted according to a deadline date, but I want them to be highlighted depending on the urgency. e.g I was using a rule with “Deadline row > (Today() + 2)” so it would highlight the dates that were two days ahead in red, but then it would highlight almost all of the dates on a monthly basis. When I create another rule with “Deadline row > (Today() + 4)” so it would highlight them in yellow for 4 days ahead, it wouldn’t do it cause the first rule was taking over the second one.
I also tried using another formula, this time on the column itself “SwitchIf(Today() < thisRow.Deadline, “on time”, Year(Today()) = 2019, “delayed”, Today() < thisRow.Deadline)” and colored them red and green, but it just shows the tasks “on time” or “delayed” without sorting them out according the urgency.
What I want is to to be able to sort my tasks by color depending on the urgency… e.g. red when the task’s date is X days after the due date, light red when there’s two days left before delivering it, or yellow when there’s 5 days.
I’ve searched on the templates but haven’t really found any useful. Hopefully someone can help me. I’d appreciate it!!

2

(I’m on mobile so I can’t test it)
You should use date range for the rules, something like this:
Red - ThisRow.DeadLine < Today ()

Light red - ThisRow.DeadLine >= Today () AND ThisRow.DeadLine < (Today () +3)`

Yellow - ThisRow.DeadLine >= (Today () +3) AND ThisRow.DeadLine <= (Today () +5)`

3

Hey Keny,

It would be really helpful if you could share the doc (here or privately with me: actinarium@gmail.com). I think it’d take me much faster to understand the issue by seeing it.

A few hints meanwhile:

  1. IIRC you can reorder conditional formatting rules, and they’ll match in order (so you have control over what overtakes what).
  2. What Asaf suggested, write your formulas / set up conditional formats to respond to ranges of dates, not just > Today() + 2
4

sure thing. here’s the link. It’s Spanglish so I hope there’s no problem with that.
Basically what I want is to have a hidden column that detects whether there’s a task delayed or is it on time, and then create a rule to highlight them depending on how close the task is to the due date.

5

Hi @Keny_Sanchez
let me know if this is your desiderable outcome:

  1. In order to decouple some complexity, I added a Delay Conditions Table, where you can put the logic of your status according to distance to Due Date
    It’s just a rough idea, but it helps to externalise rules and make them more readable/adjustable (currently, you have to be careful of days intervals consistencies)

  2. Further, I added a Deadline Days column to keep things tidy and help with the sorting.

  3. Finally, you can just define conditional formatting rules according to your status.

Does it make sense?

6

omg! yes! this is exactly what I needed! thank you so much!

7

You are welcome!
Enjoy :wink: