Tardy Identification System

Options

I am able to flag down if the 'Date Completed' surpassed the 'Deadline'; however, I am unsuccessful in making a tiered tardy system: Ontime, Late (+30 days late), and Super Late (+60 days late).


=IF([Date Completed]@row - Deadline@row > 0, "LATE", IF([Date Completed]@row - Deadline@row <= 0, "ON TIME", IF([Date Completed]@row - Deadline@row < 90, "SUPER LATE")))


When I changed the 'Date Completed' to be greater than 90 days, my formula is not able to recognize this.


Tags:

Answers

  • Genevieve P.
    Options

    Hi @rdief

    You're close!

    Nested IF statements will stop as soon as they find the first match, meaning that the order you place them in is important.

    Right now, your first statement is looking to see if the date is greater than 0, which would include 60+ days as well. Let's swap around your IF order and that should do the trick!

    =IF([Date Completed]@row - Deadline@row > 60, "SUPER LATE", IF([Date Completed]@row - Deadline@row > 0, "LATE", IF([Date Completed]@row - Deadline@row <= 0, "ON TIME")))


    (Note - I also swapped around the > sign so it's looking for greater than 60 days. You can change this back to 90 if you meant 90 days!)

    Let me know if this works for you,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • rdief
    rdief ✭✭
    Options

    Thank you! I found out after trying so many times! But I didn't know. Appreciate it mate!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!