Auto changing status based on due date

dave.mcpherson56751

Hi, i am hoping this is possible and someone can help?

We have created a sheet which is populated from a form and is working really well. We populate the "Required completion date" base on the type of issue and this is automated and again working very well.

What i would like to achieve is an automatic change to the status when the required completion date is in the past.

We currently have 4 status options. Not Started, In Progress, Complete and Overdue. 

Is it possible to change the status to "Overdue" if the "Required Completion Date" is in the past and the status is not completed?

Any help would be wonderful.

 

Thank you

Dave

Guilherme Ribeiro

Hi dave, whats up 

I did not tested but did you try to use =today() - Required Completion Date. I would create a report and request that information on it. So as the smartsheet refresh the page everytime you open the report file, it will always get updated based on today() value. You also might create a formula based on n days you got from the previous formula you uptahe the cell with overdue text

maybe?  

dave.mcpherson56751

Hi Guilherme,

thank you for responding, i don't think it is quite what i am looking for unfortunately. i have a conditional format that highlights all the tasks that are past today in Red. from that i manually go in and update their status to overdue which feeds to my dashboard.

What i want is to take away the manual update of status for overdue and have it automated on my sheet when past today's date, if that is indeed even possible?

thank you Dave

J. Craig Williams

Dave,

It can be done.

The issue is that the [Status] field can not be both manual and set to a value with a Formula (this can be done using outside tools but not a formula without Smartsheet)

For simple [Status] lists like this (Not Started, In Progress, Complete and Overdue), it should be easy to come up with some rules on when they move from one state to another and then build the formulas to do so.

The attached images shows a more complicated example.

Craig

dave.mcpherson56751

Hi Craig,

thank you for coming back to, have to admit you are on a completely different level to me as only been using SS for a short while.

i will persevere with doing things manually for now as have no idea of how to set something this complex up.

Really appreciate you taking the time to respond, thank you

Dave

J. Craig Williams

There a several (many) posts on the Community about this topic. 

Here's some guidelines:

1. List the status

2. Determine how you 'know' that the status is X or Y or ...

3. Determine if there is something in your process or already in the sheet that can be used to tell the system that 

- for example, if [% Complete] = 0 then we haven't started yet.

4. Discuss 1-3 with your team and refine

5. Add items that are missing so the system will know the status

- for example, if there "in progress" and an "in progress - at risk" status needed by the team, add an [At Risk] check mark to let the user alert the system to that.

6. And then build the formula to match your requirements.

Knowing your requirements is the most important thing.

Craig

Brian W

You might consider adding a separate Overdue Status column that automatically updates with a formula like =IF([CompletionDate] > TODAY(), "Overdue", "").

dave.mcpherson56751

Hi Brian,

That is a really good idea, the only other head Scratcher i would have is to exclude this rule if the status column was "complete". Do you think something could be added to the formula to accomodate that?

Really appreciate you taking the time to help

Dave

 

Paul Newcome

You would have to use a nested IF statement or an IF(NOT(Status@row = "Complete") kind of statement in the separate column, and if you are going that route you may as well use a nested IF statement in your original column to establish the criteria for each status type and have the whole thing automated.

 

First establish your criteria for each status type.

 

If the % Complete = 0 and the Due Date is after than Today then "Not Started"

 

If the % Complete is greater than 0 and less than 100, and the Due Date is less than Today then "In Progress"

 

If the % Complete is 100 then "Complete"

 

If it is not Complete and the due date is today or in the past then "Over Due" 

 

Then from there, build your nested IF to cover each of the types.

 

Something along these lines should cover it:

 

=IF([% Complete]@row = 1, "Complete", IF([Required Completion Date]@row <= TODAY(), "Over Due", IF([%Complete]@row = 0, "Not Started", "In Progress")))

 

This is the break down: If the % Complete is 100%, then it will say "Complete" and stop. If it is not 100% and if the due date is today or in the past, it will say "Over Due" and stop. If it is not 100% and the Due Date is in the future, then it will check the % Complete to see if it is 0. If it is then it will show "Not Started" and stop. If it is not "Complete" or "Overdue" or "Not Started", then the only other option would be "In Progress".