Activity status column based on due date and % completion

PMOguru

Team,

It will be great if someone can help me to Color Code/Highlight the activities in my smartsheet based on target completion date & % Completion column that is manually updated.

What I would like to do is:

IF target completion date is in the past & % Completion is less than 100% mark it as “PAST DUE”

IF target completion is within next 30 days and % completion is less than 90% mark it as “DUE IN 30 DAYS”

If % completion is 100% mark it as “COMPLETE”

If target completion date is within next 60 days and % completion is more than 80% mark it as “On PACE”

Thanks,

Genevieve P.

Hello,

The formula you are looking for would be an IF(AND formula, that uses the TODAY function to determine dates. I created this example based on the column headers you provided in your screen capture:

=IF([% Completion]@row = 1, "COMPLETE", IF(AND([Target Completion]@row < TODAY()), "PAST DUE", IF(AND([% Completion]@row < 0.9, [Target Completion]@row > TODAY(), [Target Completion]@row <= TODAY(30)), "DUE WITHIN 30 DAYS", IF(AND([% Completion]@row > 0.8, [Target Completion]@row > TODAY(), [Target Completion]@row <= TODAY(60)), "ON PACE"))))

 

To break this down, separate out the "IFs" to see what each statement says:

 

=IF([% Completion]@row = 1, "COMPLETE",

If the percent is 100, say "COMPLETE"

IF(AND([Target Completion]@row < TODAY()), "PAST DUE",

If the date is less than today, or in the past, say "PAST DUE"

*Note, since the percent at 100 was the first statement, the formula will first look for the percent and will still say "Complete" if the percent is 100 but the date is in the past.

IF(AND([% Completion]@row < 0.9, [Target Completion]@row > TODAY(), [Target Completion]@row <= TODAY(30)), "DUE WITHIN 30 DAYS",

If the percent is less than 90, and the date is larger than today, but less than or equal to 30 days, say "Due within 30 Days"

IF(AND([% Completion]@row > 0.8, [Target Completion]@row > TODAY(), [Target Completion]@row <= TODAY(60)), "ON PACE"))))

If the percent is greater than 80, and the date is larger than today, but less than or equal to 60 days, say "ON PACE"

 

For any other option, say for if the target completion is greater than 60 days, there will be a blank cell. You can read more about each of these functions via our Help Center:

• IF statements
• AND statements
• TODAY function
• @row function

 

Thanks!



Genevieve

PMOguru

Thank you. Appreciate it!

if i may, one more Question/ thought i had is I want color code the cell for target Completion % as users enter their % completion

EX. if start date is 1 august 2019, end date is 31st August and Today is 26 August 

Target % completion is 26/31 = 83% 

so if Users put 80% I want that cell to become yellow as it is within 10 % of % completion. 

If it is behind by more than 10% ..It needs to be RED.

If its more than target completion % ....should be Green. 

Please let me know if it's possible?

Thanks

 

Genevieve P.

Yes, this would be possible. I would suggest setting up two new columns in your sheet:

• Target %
• Difference of %

 

In your “Target %” column, you could create a formula that takes Today - the start date of the task in the Start Date column, and divides by 31 to receive the target percent for that day:

=(TODAY() - [Start Date]@row) / 31

 

Then, in your “Difference of %” column, take the Target % minus the actual % Complete to find the different between them:

=[Target %]@row - [% Complete]@row

 

Make sure both of these columns are set up to be in Percentage Format.

Finally, you can set up Conditional Formatting rules to say that if the Difference column displays a certain percentage, to change the background colour of the cells in the % Complete column.

I have attached three screen captures below that show an example of each of these steps.