Forum Discussion

beccaw745's avatar
beccaw745
Copper Contributor
Jul 19, 2024

Help with formula

Hi!

I'm new to excel and am trying to make a date list for my job. I'm wanting to have an expiration date automatically turn colors depending on status. Example, EXP : 6/02/2025 turn it green until 4/02/2025, yellow once 4/02/2025 hits until 5/02/2025 and then turn red on that date. Can anyone help? TIA

  • beccaw745 

    Select the range with the expiration 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(), 2)

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

     

    Repeat these steps, but with

    =EDATE(TODAY(), 1)

    and red as fill color.

     

    If you have blank cells in the range, add another rule with 'equal to', ="" and No color.

  • beccaw745 

    Select the range with the expiration 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(), 2)

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

     

    Repeat these steps, but with

    =EDATE(TODAY(), 1)

    and red as fill color.

     

    If you have blank cells in the range, add another rule with 'equal to', ="" and No color.

Resources