Conditional formatting for reoccurring tasks

jacummings

Hello, I am trying to design a task tracking smartsheet for tasks of varying reoccurrence, i.e., monthly, quarterly and annually. In this sheet, the idea is to use conditional formatting as a means to identify when something is past due (in the past, red), upcoming (in the next 60 days, yellow), completed (green) or in the next year (gray). Please see the attached screenshot.

My plan was to use conditional 'if' formatting based on a completion percentage, the listed date of the deliverable and the current date, which would assign the aforementioned colors based on the dates and completion percentage. Although this works for an annual task, it doesn't work for quarterly or monthly, as the completion percentage then applies conditional formatting to every month column. Having a separate completion percentage column beside each monthly column is considered too cluttered to be a viable solution. I have demonstrated this in the attached screenshot.

What would be the best way to handle what I'm trying to accomplish with conditional formatting? Any formula suggestions would be appreciated.

Example of Tasks.png

Genevieve P.

Hi @jacummings

You're right - you would need some sort of indicator on the row to associate with each column. The formatting rules won't be able to identify if "100%" should only apply to a specific column or not.

What I would do in this instance is set up a Multi-Select column instead of a Percent Complete. This way you can select the Month that's been completed in a dropdown, and use this for your Conditional Formatting:

This way you can indicate when something is complete.

Now you can set up your Conditional Formatting rules per-date-column to search for both the correct dropdown selection and if the date is in the past or not:

Let me know if that makes sense and works for you!

Cheers,

Genevieve