Conditional formatting based on time elapsed from date

Copper Contributor

Hi there!

 

I'm trying to figure out how to make a specific cell in a column appear a certain color based on the amount of time that has elapsed from a date in the cell in the adjacent column... If that makes sense...

So, if cell G4 has a date in it for the last contact with an individual, I want cell H4 to appear green if that date is within the last 30 days, yellow if it's between 31-60 days ago, orange for 61-90 days ago, red for 91-120 days ago, and black for anything over 120 days ago. I then want to make sure this is a blanket format so that the same rule applies to any cell in the G/H columns (so same rule will apply for G5/H5, G6/H6, and so on).

I saw another post referencing a date being more or less than today's date in months:

=EDATE(G2,3) <=TODAY()

but I wasn't sure if there was a way to do it in days past vs months?  If not, I can make months work.  I just don't know how to make it a blanket format (vs inputting the formula in each individual cell) w/ multiple color options?

 

Thanks in advance!

1 Reply

@Liz_Schaffer 

Select G4:H100 or as far down as needed. G4 should be the active cell in the selection.


On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=$G4<=TODAY()

Click Format...
Activate the Fill tab.
Select green as fill color.
Click OK, then click OK again.

 

Repeat, but with

=$G4<=TODAY()-31

and yellow as fill color.

 

Then repeat again with

=$G4<=TODAY()-61

and orange.

 

Then with

=$G4<=TODAY()-91

and red.

 

Finally, with

=$G4<=TODAY()-121

and black.