#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Conditional formatting for how overdue something is

edited 12/09/19

Hi all

I am trying to use conditional formatting to change the colour of the date cell for when the date cell is over a week ago, and then another color for when the date cell is over a month ago.

I think I may be missing something!

So in effect, the cell would be normal when under 7 days overdue, when 7 days overdue it changes to one colour, then it changes to another colour when reaching 30 days overdue.

I was able to do this in google docs, but unsure how to do it here!

• edited 05/17/16

Hello Robb,

My advice to you would be to first greate a Date column if you do not already have one, then under condition you can now pick the date column and say "is in the last (7/30) days" and go from there.

Hope it helps!

• You can try a variance of a formula I use to identify tasks that are past due or due within the next 7 days and are not at 100% completion.

I have added a column "Due/PAst Due" and use this formula in that column:

=IF(AND(Due33 <= TODAY() + 7, NOT(Due33 < TODAY()), NOT([% Complete]33 = 1)), "Due within 7 Days", IF(AND(TODAY() > Due33, NOT([% Complete]33 = 1)), "Past Due", ID33))

I have then used conditional formating to highlight cells with "Due within 7 Days" in yellow and those with "Past Due" in Red.

This discussion has been closed.