Forum Discussion

Lennox2525's avatar
Lennox2525
Copper Contributor
Sep 17, 2024
Solved

Excel Help with Conditional Formatting -dates

I need help with a formula for - Green > one month past todays date, Red =< todays date, amber <= one month todays date.

 

 

 

  • Lennox2525 

    Select the cells with the dates. Set the fill color to green. This will be the default.

     

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    Select 'Format only cells that contain'.
    Leave the first drop down set to 'Cell Value'.
    Select 'less than or equal to' from the second drop down.
    In the box next to it, enter the formula

    =EDATE(TODAY(), 1)

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

     

    Repeat these steps, but with

    =TODAY()

    and red as fill color.

     

    Finally, repeat with 'equal to' instead of 'less than or equal to', with

    =""

    and No Color as fill color.

    This removes the fill color from empty cells.

  • m_tarler's avatar
    m_tarler
    Bronze Contributor

    you have a couple options:
    you can set up at 2 or 3 conditional formatting rules each one using a formula like <=today()
    or you can use 3 color rule and base each color on 'number' and use the same date rules

    the advantage of the multiple rules is you get 'pure' color while the above single rule approach give a gradient when you transition

     

  • Lennox2525 

    Select the cells with the dates. Set the fill color to green. This will be the default.

     

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    Select 'Format only cells that contain'.
    Leave the first drop down set to 'Cell Value'.
    Select 'less than or equal to' from the second drop down.
    In the box next to it, enter the formula

    =EDATE(TODAY(), 1)

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

     

    Repeat these steps, but with

    =TODAY()

    and red as fill color.

     

    Finally, repeat with 'equal to' instead of 'less than or equal to', with

    =""

    and No Color as fill color.

    This removes the fill color from empty cells.

Resources