Activity status column based on due date and % completion

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,

Find more posts tagged with

Sheets

Reports

Project Management

Comments

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:

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.

Target Percent Formula.png

Difference in Percent Formula.png

Conditional Formatting .png

Sign in to join the conversation!

Quick Links

Trending discussions

Blank rows appearing on the bottom of Report and embedded reports
I have a report that will generally only have about 15 rows on it. I see in other threads that Smartsheet will automatically create enough blank rows to reach 50, or at least 10 blank rows always at the bottom. Even if I delete the blank rows from the Sheet (which has hundreds of data rows), they reappear the next time I…
Automating Quarterly/Annual Reminders
I need to setup automations based on a contract date that will notify me on a monthly/quarterly/annual basis so I can process paperwork at the appropriate time. Ideally, I'd like to set it up to send an email 90 days before that date. I have columns for the initial contract date and the frequency (one for the word…
Set Dashboard as Default Homepage for All Users
Hi everyone, Is there a way to set a dashboard as the default homepage for all users upon login? Ideally, we’d like users to land directly on a particular dashboard instead of the usual default page. Is this possible through configuration, or is there a plugin or workaround that can help us achieve this? Thanks in advance!