Add requirement to formula is a cell is blank versus not blank

kdf3

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.

 

Etienne Desbiens

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

kdf3

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?

Etienne Desbiens

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

kdf3

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.

kdf3

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