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

ashdrap

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.

Paul Newcome

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

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

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

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

@Paul Newcome we are trying for exactly that

Paul Newcome

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

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

ashdrap

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

Hollie Green

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

That would go in the [At Risk] column.