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.
