IF/AND - Red, Yellow Green Formula

RMikelson

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!

Nic Larsen

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"

Nic Larsen

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"

RMikelson

That works, thanks so much!