Need to add ISBLANK to a formula

Stephanie Stewart

Good morning,

To determine the status of a row, I have the following formula (in the Status) column:

=IF(Complete1 = "Yes", "COMPLETED", IF(Complete1 = "Cancelled", "CANCELLED", (IF(TODAY() >= [Due Date]1, "PAST DUE", IF(TODAY(1) = [Due Date]1, "Due Tomorrow", "In Progress")))))

I need to add to this formula to leave the cells in the status column blank if the 'Due Date' field is blank. I've tried several variations of the following, but get an error each time.

=IF(Complete1 = "Yes", "COMPLETED", IF(Complete1 = "Cancelled", "CANCELLED", (IF(TODAY() >= [Due Date]1, "PAST DUE", IF(TODAY(1) = [Due Date]1, "Due Tomorrow", "In Progress", IF(ISBLANK([Due Date]1), " "))))))

What am I missing?

Thank you!

Paul Newcome

It is just the order in which your IF's are nested. Smartsheet works from left to right and will stop at the first true value.

=IF(ISBLANK([Due Date]@row), "", IF(Complete1 = "Yes", "COMPLETED", IF(Complete1 = "Cancelled", "CANCELLED", (IF(TODAY() >= [Due Date]1, "PAST DUE", IF(TODAY(1) = [Due Date]1, "Due Tomorrow", "In Progress"))))))

Stephanie Stewart

Hi Paul,

Thanks so your help and insight on how Smartsheet works (from left to right). Now it makes sense and is working! Thanks again!

Paul Newcome

It is just the order in which your IF's are nested. Smartsheet works from left to right and will stop at the first true value.

=IF(ISBLANK([Due Date]@row), "", IF(Complete1 = "Yes", "COMPLETED", IF(Complete1 = "Cancelled", "CANCELLED", (IF(TODAY() >= [Due Date]1, "PAST DUE", IF(TODAY(1) = [Due Date]1, "Due Tomorrow", "In Progress"))))))

Stephanie Stewart

Hi Paul,

Thanks so your help and insight on how Smartsheet works (from left to right). Now it makes sense and is working! Thanks again!

Paul Newcome

Happy to help! 👍️