Nested IF: Flagging overdue tasks automatically

[Madison_Sturges]

Hi all! I’m rather new to formulas and the like and was hoping to solve the following:

If task in my “Status” Column is anything other than “Complete” or “Archived”, AND it is past “Targeted Completion” Date, then “Status” should read “Overdue”.

Here’s how far (not far) I got: IF(IS_BEFORE({Target Completion}, TODAY()),“Overdue”))

Any help you can offer would be greatly appreciated! Cheers :grinning_face_with_smiling_eyes:

[Jeremy_Oglesby]

I believe this is what you are looking for:

IF(
  AND(
    IS_AFTER(
      {Target Completion},
      TODAY()
    ),
    OR(
      Status != "Complete",
      Status != "Archived"
    )
  ),
  "⏰ Overdue ⏰",
  BLANK()
)

(remove emoji as desired)

[Madison_Sturges]

Thanks for your reply!
Quick question: Should it not be “BEFORE” and not “AFTER”?
Also, after running it I have a feeling this function isn’t necessarily what I’m after. That being said, you’ve laid out a really good foundation, I think I can work on it from here :slightly_smiling_face:
Cheers!

[Jeremy_Oglesby]

Madison_Sturgess:

Quick question: Should it not be “BEFORE” and not “AFTER”?

Yes, you’re right - sorry. My brain naturally goes the other way when thinking about date comparisons (I want compare TODAY() in relation to my deadline, rather than compare my deadline in relation to TODAY()), so I used AFTER(), but then I had already copied your field names and just pasted them into my formula and forgot to switch them around!

Post back if you need help expanding on it to do what you need it to.

[Madison_Sturges]

No problem! Appreciate the speedy response. Will see how I go :slightly_smiling_face: :v:t4:

[Maricris_Malamu]

Hello, i tried this but I am still getting an error saying something is wrong with the formula. I am relatively new to airtable and I did change the fields. Thank you for all your help