Jul 02 2024 11:47 AM
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!
Jul 02 2024 02:21 PM
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.