At Risk Flag turning red based on Start & End Date Formula

Options
Dora
Dora ✭✭
edited 03/15/23 in Formulas and Functions

Hello everyone!


I am trying to automate some of the project plans, and I was wondering what the best way is to express this in a formula:

If Status is Not Started by 3 days after the Start Date, and in any other Staus but Completed by the End Date, mark the flag red in the At Risk column

@Andrée Starå Thank you!




Tags:

Best Answer

  • ericncarr
    ericncarr ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Dora,

    Here's the formula I came up with based on the criteria you put, hope it's helpful!

    Basically, if today's date is greater than or equal to the Start Date + 3 AND the Status is equal to "Not Started", the flag will activate. If today's date is greater than or equal to the End Date AND the Status is not equal to "Completed", the flag will also activate.

    =IF(AND(TODAY() >= ([Start Date]@row + 3), Status@row = "Not Started"), 1, IF(AND(TODAY() >= [End Date]@row, Status@row <> "Completed"), 1, 0))

    Let me know if that works or if you have any questions.

Answers

  • ericncarr
    ericncarr ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Dora,

    Here's the formula I came up with based on the criteria you put, hope it's helpful!

    Basically, if today's date is greater than or equal to the Start Date + 3 AND the Status is equal to "Not Started", the flag will activate. If today's date is greater than or equal to the End Date AND the Status is not equal to "Completed", the flag will also activate.

    =IF(AND(TODAY() >= ([Start Date]@row + 3), Status@row = "Not Started"), 1, IF(AND(TODAY() >= [End Date]@row, Status@row <> "Completed"), 1, 0))

    Let me know if that works or if you have any questions.

  • Dora
    Dora ✭✭
    Options

    That worked PERFECTLY! Thank you so much!

  • ericncarr
    ericncarr ✭✭✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!