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

Robb Parris
edited 12/09/19 in Archived 2016 Posts

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!

 

Thanks for any help you can give me!

 

 

Comments

  • Ichiorochi
    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.