Forum Discussion

Liz_Schaffer's avatar
Liz_Schaffer
Copper Contributor
Jul 02, 2024

Conditional formatting based on time elapsed from date

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!

Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 02, 2024

    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.

Resources