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

kdf3
kdf3
edited 12/09/19 in Archived 2016 Posts

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

This discussion has been closed.