Help with Flagging Overdue or At-Risk Tasks

[ellllllllllle]

I have a formula that is achieving 2/3 of my goals, but I need some help figuring out that last 1/3! I am trying to add an "SOS" flag to any to-do that meets one or more of the following criteria:

1. Has been manually flagged as "Behind" in the Alerts column.
2. Has not been given a Due Date (so the Due Date field is blank).
3. Has a Due Date that has passed AND it has NOT yet been marked as "Done."

How would I structure that final part of the formula? Currently, I have:

IF(

     OR(

          Alerts = "Behind",

          {Due In} < 1

     ),

          "🆘",

          ""

)

I am trying to get the "Post job description on social media" task to be flagged, since it is overdue and not marked as done.

Thank you so much! 

[CJSmith · Accepted solution]

Hi @ellllllllllle, the last component to your OR() clause should be as follows:

AND(DATETIME_DIFF({Due Date}, TODAY(), 'days')<0, {Status}!="Done")

Altogether, the formula should look like this:

IF(
  OR(
    {Alerts}="Behind", 
    {Due In}<1, 
    AND(DATETIME_DIFF({Due Date}, TODAY(), 'days')<0, {Status}!="Done")), 
  "🆘", "")

Let me know if that works!

[ellllllllllle]

@CJSmith It worked, thank you so much!

[CJSmith]

You got it!