Forum Discussion

Arcadia84's avatar
Arcadia84
Copper Contributor
Jul 04, 2024

How to make multiple "date" rules against "today" in a single column of data

Hi,

I have to monitor many clients and be able to order the data based on when they were referred in from oldest to newest. But in addition, I would like to make them RED, AMBER or GREEN based on how old they are against the current date (ie today).

EG;

- More than 2 days old = GREEN

- More than 3 days old = AMBER

- More than 5 days old = RED


Can someone tell me the formula I should use, or how I can set the rules under conditional formatting?

 

Thanks

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

    Arcadia84 

    Let's say the referral dates are in D2:D100.

    Select this range, or if you want to color multiple columns, select the entire range that you want to color.

    The active cell in the selection should be in row 2.

     

    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

    =AND($D2<>"", $D2<TODAY()-2)

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

     

    Repeat, but with

    =AND($D2<>"", $D2<TODAY()-3)

    and amber as fill color.

     

    Repeat again with

    =AND($D2<>"", $D2<TODAY()-5)

    and red as fill color

Resources