How to Format Date-dependent Color-changing Cells (3 total columns)

I need to create a Smartsheet for tracking documents, the date of last review and a color changing column reflecting the date of next review.

In column A I need to list the document names.

In column B I need to list the date of last review.

I need help to format column C cells.

1. I need for Column C to populate a date that is 2 years or 24 months from column B's date.

2. Then, when the date in column C cell is 2 years minus 30 days (or 23 months), I need the date to turn yellow for the entirety of those 30 days. (Meaning, I need column C to turn yellow for the entire 23rd month/2 years-30 days.) This is to help us track that we are within a 30 day window, and the document needs review.

3. When the date in column C reaches and passes or is >= 2 years, I need the cell to turn red.

When the date in Column B is updated (as in, the document is reviewed), I need the cell to go back to it's original state, with no color.

****** I NEED THE DATE TO TAKE INTO CONSIDERATION LEAP YEARS/REAL CALENDAR ********

Example:

A B C

Document 1 1/31/2021 1/31/2023 (RED) -past 24 months

Document 2 3/3/2023 3/03/2025 (NO COLOR) -under 23months

Document 3 3/1/2022 3/01/2024 (YELLOW) -ENTIRETY of time between 23-24 months

How do I do this?

Find more posts tagged with

Templates

Document Builder

Conditional Formatting

formatting

Accepted answers

BMauney

I finally figured out how to apply the data you gave me to make the correct color display at the right times.

Here is what I ended up with:

Formula:

Conditional formatting:

One key tip was to ensure the most important priority rule was at the top, which I kept forgetting to do.

Also, making sure my titles were correct, without a single error/misspelling and matched upper or lower cases. I did "Last reviewed," and "Last Reviewed" and was getting incorrect information.

This is how new to Smartsheet I am.

Thank you for all of your help!

All comments

Paul Newcome

Column C would use a formula along the lines of

=DATE(YEAR([Column B]@row) + 2, MONTH([Column B]@row), DAY([Column B]@row))

Then you would use conditional formatting to change the cell colors in Column C.

BMauney

Thank you.

Could I please request further help in formatting the color change to meet the criteria.

Yellow = within the entirety of 23 months/2 years minus 30 days.

Red = Greater than or equal to 24 months/2 years

I was able to correctly populate dates 2 years from the review date given the formula.

Do I need "two" formulas?

Paul Newcome

You do not need additional formulas. You can use the Conditional Formatting tool.

BMauney

Thank you for your answer.

I do not know how to use the conditional formatting tool to make these cells change colors for the specific needs, which is primarily the reason for the original post.

How do I get the cells to change colors, back and forth between yellow, red, or no color, dependent upon the date?

(I only have just learned I do not need additional formulas, thanks to you, but I also do not know how to make the cells change colors, specifically for the reasons listed above). I know how to make it change for one, but I do not know how to make it change for both, or how to set up the format condition.

Thanks again!

Paul Newcome

You would create rules similar to these:

BMauney

I finally figured out how to apply the data you gave me to make the correct color display at the right times.

Here is what I ended up with:

Formula:

Conditional formatting:

One key tip was to ensure the most important priority rule was at the top, which I kept forgetting to do.

Also, making sure my titles were correct, without a single error/misspelling and matched upper or lower cases. I did "Last reviewed," and "Last Reviewed" and was getting incorrect information.

This is how new to Smartsheet I am.

Thank you for all of your help!

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.