Exclude blank "Due Date" cells from formula

Michael C
Michael C
edited 12/09/19 in Formulas and Functions

I'm trying to update my "At Risk" column (Flag icon ON/OFF).  I want to create a nested formula. I want the flag ON if all three conditions are met:

  1. The "Due Date" is within one day.  Example: if today is 2018-05-04 and the "Due Date" is 2018-05-03, then this condition is met.
  2. I have a "Status" column.  If one of these values is met, then this condition is met:
    1. Terminated
    2. Complete
    3. On Hold
  3. The "Due Date" column needs to have a date value.  If the "Due Date" is blank, then I do NOT want this condition to be met.  
    1. This is where I'm STUCK.
    2. For example, some parts of the project are not well defined or will happen far, far into the future. We don't even know when the due date is, so we'd rather keep the cell blank.  I do NOT want the "At Risk" flag to turn on because of a task with no due date.

Here is the start of my formula.  Can someone please help me add condition #3 to my existing formula?


=IF(AND([Due Date]1 <= TODAY() + 1, Status1 <> "Complete", Status1 <> "Terminated", Status1 <> "On Hold"), 1, 0)




  • Michael,

    I would write your formula the following way assuming that "Due Date" has been set to the Date column type:

    =IF(SUM(IF([Due Date]1 <= TODAY() + 1, 1, 0), IF(OR([Status]1 = "Complete", [Status1] = "Terminated", [Status]1 = "On Hold"), 1, 0), IF(ISDATE([Due Date]1), 1, 0) <> 0, 1,0)

    Try that out and see if it works.

  • I can confirm that my "Due Date" column has been set to the Date column type.  However, I'm getting #UNPARSEABLE when I use this formula.  I simply copied and pasted this formula in Row 1, but I got the error.  What could the issue be?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!