Update status field based on End date and %completion

Hello All,

I would like to do some conditional updates in smartsheet based on below:

1) I want status field to reflect Green if End data of task is greater than today's date

2) I want status field to reflect Red if End data of task is less than today's date and % completion is less than 100%.

3) I want status field to reflect Amber if End data of task is greater than today's date but within 1 week from today's date and % completion is less than 100%.

Please advise how can I put this formulae.

Find more posts tagged with

Formulas

IT & Operations

Conditional Formatting

Comments

SmSulli

With the Status column formatted for symbols, here is the formula using Red, Yellow, and Green balls.

=IF([End Date]1 > TODAY() + 7, "Green", IF(AND([End Date]1 < TODAY() + 7, [End Date]1 > TODAY(), [% Complete]1 < 1), "Yellow", IF(AND([End Date]1 < TODAY(), [% Complete]1 < 1), "Red", "Green")))

Give it a try!

Community Help.JPG

J. Craig Williams

When I tried the formula, I got incorrect results for TODAY() and TODAY()+7.

I also noticed that TODAY()+7 was in the final IF FALSE conditions (ie "everything else") so I rewrote it:

=IF(AND([End Date]34 <= TODAY() + 7, [End Date]34 >= TODAY(), [% Complete]34 < 1), "Yellow", IF(AND([End Date]34 < TODAY(), [% Complete]34 < 1), "Red", "Green"))

if today through a week from now (inclusive) and not completed is yellow.

if not completed and past due is red

otherwise green.

When building formulas, make sure all cases are checked.

Craig

Bill Repucci

I did my status markers slightly different, but only because I export each person's task to Excel and send them a list. (If SS can aggregate all the tasks for one person and auto send them, based on dates and % complete, please inform me how to do this.)

Here is my formula. I first check to see if the task name is blank. This is required so you can drag the formula down the entire column.

Then I check to see if it is complete (100%). If it is not, then I check to see if the task finish date has passed. If it has passed, then I flag the task as "*** LATE ***". Next is to mark it either "In Process" or "Not Started".

I will probably add a notification tied to the start date, so that if the work has not stared and the start date has passed, it will notify me.

=IF(NOT(ISBLANK([Task Name]1)), IF([% Complete]1 = 1, "Completed", IF(Finish1 < TODAY(), "*** LATE ***", IF(AND([% Complete]1 > 0, [% Complete]1 < 1), "In Process", IF([% Complete]1 = 0, "Not Started")))), "")

J. Craig Williams

re: (If SS can aggregate all the tasks for one person and auto send them, based on dates and % complete, please inform me how to do this.)

Build a Report for the person. Send Attachment can be scheduled.

Craig

Bill Repucci

Thanks Craig, I'll create the custom reports, as you suggest.

On another note, I updated the formula I include above.

This version of the Status formula performs the following:

1. Check to see if the % Complete is less than 100%. If so, move on to the next check. Otherwise, mark the Status at "Complete".

2. Check to see of Task Name or Start date is blank. If either are blank, put nothing in the Status field.

3. Check the Finish date and if it is less than today, mark the WBS as "*** LATE ***".

4. If the scheduled Finish date is within a week, mark it as "Due w/in a Week".

5. If the scheduled Start date is within a week, mark it as “Scheduled start w/in a week”.

6. If the % Complete is greater than zero, mark it as “WIP” (Work In Process).

(Checks 4, 5, and 6 are biased towards reporting "Due w/in a Week". So something could be a WIP but I want to know that it is due within the next seven days.)

7. If the Start date has passed, mark it as “Late Start”.

8. At this point the only thing left is “Future Work”.

The formula:

=IF([% Complete]1 < 1, IF(NOT(OR(ISBLANK([Task Name]1), ISBLANK(Start1))), IF(Finish1 - TODAY() < 0, "*** LATE ***", IF(Finish1 - TODAY() < 7, "Due w/in a Week", IF(AND(Start1 - TODAY() > -1, Start1 - TODAY() < 8), "Scheduled start w/in a week", IF([% Complete]1 > 0, "WIP", IF(Start1 < TODAY(), "Late Start", "Future Work")))))), "Complete")

With the status column filled out, I plot the status of each Resource’s tasks and the status of the entire project in Excel.