Conditional formatting for how overdue something is

Robb Parris

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!

 

 

Ichiorochi

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!

Tariq Shaikh

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.