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


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


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


Repeat, but with


and yellow as fill color.


Then repeat again with


and orange.


Then with


and red.


Finally, with


and black.