Hello! I am looking for help with a formula. I am trying to do at risk/overdue/not started. Do I mak

Options

Hello! I am looking for help with a formula. I am trying to do at risk/overdue/not started. Do I make separate columns for all three and what would the formula be? I am making a dashboard and want the at risk and overdue to go by the Deadline.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You should be able to use a single column for it and a nested IF formula. What are your criteria for each status, and are you able to provide a screenshot for reference?

  • ashdrap
    ashdrap ✭✭
    Options

    this will probably sound jumbled as I am still learning SmartSheet. I am looking for a formula that will be pretty much at risk if there are 3 days to deadline and overdue if it is 1 day past deadline. I don't exactly have a reference because I am still trying to figure out the formulas and columns. We only have a deadline column but no overdue/at risk. We also have the status column that says not started/in progress/etc etc


  • Christian Graf
    Christian Graf ✭✭✭✭✭
    edited 01/06/23
    Options

    Hello @ashdrap,

    I have typically seen a [Percent Complete] column in addition to the ones you have, where the assignee returns daily to update the percent complete for their task.

    That percentage then drives the [Status] column which will say "Not Started" at 0%, "In Progress" at 1-99%, and "Complete" at 100%.

    The [Overdue] column is typically driven by the deadline like you said.

    Here is a typical formula for an [Overdue] column:

    =IF(NETDAYS(Deadline@row, TODAY() - 1) > 0, "Overdue", IF(NETDAYS(Deadline@row, TODAY()) > -4, "At Risk", "On Track"))

    Here is a formula for a [Status] column tied to a [Percent Complete] column:

    =IF([Percent Complete]@row = 0, "Not Started", IF([Percent Complete]@row = 1, "Complete", "In Progress"))

    You can complicate it further if you want to tie the [Overdue] column and [Percent Complete] columns together. It all depends on what you want displayed based on what conditions occur. You can have statuses like: "In Progress - On Track", "In Progress - At Risk", "In Progress - Overdue", etc.

    You would need to use a growing list of nested if statements for this option.


    Hope this helps!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    What are the different statuses, and what would be the requirements for each?


    Something like...

    Not Started and within 5 days of the deadline is At Risk

    Not started and past the deadline is Overdue

    In Progress and within 3 days of the deadline is At Risk

    In Progress and past the deadline is Overdue

    Complete is blank.

  • ashdrap
    ashdrap ✭✭
    Options

    @Paul Newcome we are trying for exactly that

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    In that case you could use something along the lines of...

    =IF(Status@row <> "Complete", IF(Deadline@row< TODAY(), "Overdue", IF(OR(AND(Status@row = "In Progress", Deadline@row<= TODAY(3)), AND(Status@row = "Not Started", Deadline@row<= TODAY(5))), "At Risk")))

  • ashdrap
    ashdrap ✭✭
    Options

    Thank you! Stupid question, would this go into a separate column? Or do I put this in the deadline column or status?

  • ashdrap
    ashdrap ✭✭
    Options

    I am getting circular reference with that formula. Is there something I might be doing wrong?


  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    edited 01/06/23
    Options

    It would need to go in a separate column because it is referencing if the status is either not started, In Progress, or Complete. That is why you are getting the circular reference is because it is in the status column and the formula references the status column.

    Another possibility would be to add a Start Date Column and a Completed Date column and use the formula in the Status column. The below will return status of Not Started, At Risk, In Progress, or Completed.

    =IF([Complete Date]@row = "", IF([Start Date]@row = "", IF(Deadline@row <= TODAY(5), "At Risk", "Not Started"), IF(Deadline@row <= TODAY(3), "At Risk", "In Progress")), "Completed")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!