RAG status based on dates overdue


I'm attempting to get an output of either, "On Track", "Overdue" or "Critically Overdue" for baseline milestone dates in Smartsheet, with the Overdue being 5 days over & Critically overdue being 20 days over respectively.

I have drafted the initial IF statement, however i'm suck on how to add the additional "Critically Overdue" parameter and how to add the 5 & 20 days threshold.

=IF([Baseline Date]@row = [Actual Date]@row, "On Track", "Overdue")

Any suggestions would be appreciated!



Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @BenM 

    Hope you are fine, please try the following formula and covert it to column format formula:

    =IFERROR(IF(OR([Actual Date]@row = "", [Baseline Date]@row = ""), "", IF([Actual Date]@row
    = [Baseline Date]@row, "On Track", IF(AND([Baseline Date]@row - [Actual Date]@row >= 5,
    [Baseline Date]@row - [Actual Date]@row <= 20), "Overdue",
    IF([Baseline Date]@row - [Actual Date]@row > 20, "Critically Overdue")))), "")

    The following screenshot shows the result:

    PMP Certified

    [email protected]


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!