Formula IF; AND; ISBLANK; NOT

Options

Need some assistance please. I looked at previous similar errors and comments however could not get this formula to work.

At Risk is a Flag - Symbol - Flag when the End Date is higher than today however the Status is not "Complete". Sometimes the End Date is blank and therefor require the ISBLANK formula.

My initial formula was:

=IF(AND([End Date]@row < TODAY(); NOT(Status@row = "Complete")); 1; 0)

-It works however also flag the empty End Date as a Risk.

Then tried some other ways however keep on getting errors:

=IF(AND(ISBLANK([End Date]@row)); [End Date]@row < TODAY(); NOT(Status@row = "Complete"); 1; 0)

Could somebody please take a look at assist me?

Thank you.

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    @yolandi.venter

    =IF(ISBLANK([End Date]@row); 0;IF(AND([End Date]@row < TODAY(); NOT(Status@row = "Complete")); 1; 0))

    See how this works out for you

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!