IF/AND - Red, Yellow Green Formula

RMikelson
RMikelson ✭✭
edited 03/06/24 in Smartsheet Basics

Hello! I'm trying to reflect the contract status based on a contract expiration date. Example - If contract does not expire for 45+ days, status is green. If the contract expires within 15-45 days, the status is yellow. If the contract expires within 15 days, the status is red. Unfortunately, the status shows as yellow if the contract expiration cell is blank and I've searched for solutions and I'm unable to find the answer. Below is the formula I'm using:

=IF([Contract Expiration Date]@row > TODAY() + 45, "Green", IF([Contract Expiration Date]@row < TODAY() + 45, "Yellow", IF([Contract Expiration Date]@row < TODAY() + 15, "Red", 0)))


I appreciate any help!

Best Answer

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓

    You could add the ISBLANK and tell it what you want to do.

    =IF(ISBLANK([Contract Expiration Date]@row), "", IF([Contract Expiration Date]@row > TODAY() + 45, "Green", IF([Contract Expiration Date]@row < TODAY() + 45, "Yellow", IF([Contract Expiration Date]@row < TODAY() + 15, "Red", 0

    IF(ISBLANK([Contract Expiration Date]@row, "Leave this blank if you want nothing or give it a status"

Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓

    You could add the ISBLANK and tell it what you want to do.

    =IF(ISBLANK([Contract Expiration Date]@row), "", IF([Contract Expiration Date]@row > TODAY() + 45, "Green", IF([Contract Expiration Date]@row < TODAY() + 45, "Yellow", IF([Contract Expiration Date]@row < TODAY() + 15, "Red", 0

    IF(ISBLANK([Contract Expiration Date]@row, "Leave this blank if you want nothing or give it a status"

  • That works, thanks so much!