#### 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

edited 12/09/19

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"))))

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.

• 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".

This discussion has been closed.