Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Add requirement to formula is a cell is blank versus not blank
Here is what I am trying to do for our contracts:
Cells I'm working with (for the formula)
• Contract Start {Date}
• Contract End {Date}
• Remaining Days {Number} // formula =[Contract End]1  TODAY()
• Contract Status {Dropdown  see below} // formula =IF([Remaining Days]1 < 0, "Expired Contract", IF([Remaining Days]1 > 30, "Open Contract", IF(AND([Remaining Days]1 < 31, [Remaining Days]1 > 7), "Contract Expiring < 30 Days", IF(AND([Remaining Days]1 < 8, [Remaining Days]1 > 0), "Contract Expiring < 7 Days"))))
Dropdown menu options (Contract Status):
• Expired Contract  Any contract that expired anytime before today
• Contract Expiring < 7 Days  Any contract that expires within 7 days from today including today
• Contract Expiring < 30 Days  Any contract that expires within 30 days but more than 7 days from today
• Open Contract  Any contract that is open (not flagged for expiring within 7 or 30 days)  these include remaining days that are greater than 30
• Open Contract (no expiration date) – Any contract without a given End Date (or Remaining Days cell is Empty)
HERE IS THE PROBLEM:
If remaining days is empty (which applies if there is not Contract End Date) it assumes it is less than 0 and marks it as Expired. How do I set up the formula to essentially say "if Remaining days is empty, mark it as "Open Contract (no....) AND if Remaining Days is not empty use <insert formula above>.
The formula works correctly for everything with a number. I think this is an easy formula, I just can't figure out where the override button is.
Thanks.
Comments

I think this is what you are looking for... You need to use the Not() and the isblank function()
=if(Not(Isblank(Remainingdays)), "Open contract (no..."), ***resto of the formula***)
Hope that helps,
Étienne Desbiens

Thank you.
It says UNPARSEABLE:
Here is my new formula:
=IF(NOT(ISBLANK([Remaining Days]78), "Open Contract (No End Date)”, IF([Remaining Days]78 < 0, "Expired Contract", IF([Remaining Days]78 > 30, "Open Contract", IF(AND([Remaining Days]78 < 31, [Remaining Days]78 > 7), "Contract Expiring < 30 Days", IF(AND([Remaining Days]78 < 8, [Remaining Days]78 > 0), "Contract Expiring < 7 Days")))))
Should the NOT actually be before the ISBLANK because that's the actual blank cell?

Hum, my error... You don't need any not since you want to know if the cell is empty, not if there is something in it!
=IF(ISBLANK([Remaining Days]78), "Open Contract (No End Date)”, IF([Remaining Days]78 < 0, "Expired Contract", IF([Remaining Days]78 > 30, "Open Contract", IF(AND([Remaining Days]78 < 31, [Remaining Days]78 > 7), "Contract Expiring < 30 Days", IF(AND([Remaining Days]78 < 8, [Remaining Days]78 > 0), "Contract Expiring < 7 Days")))))
This should work! Sorry about that!
Hope that helps,
Étienne Desbiens

Hmm, it still says unparseable....
Just to double check, removed – ISBLANK([Remaining Days]78), "Open Contract (No End Date)”, – and a ")" at the end and it is working fine.
That ISBLANK formula is not working for me.

In the meantime I am bandaiding it with a condition to just turn the text white so you can't see it say "expired".