At Risk Formula that utilizes end date and status columns only...

StephMac ✭✭
edited 10/20/22 in Formulas and Functions

I am stumped. I have tried various formulas only for them to error. The closest I have come is the following but I am missing pieces.

=IF([End Date]@row = "", 0, IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), 1))

BUT... I need it to do more:

  • I need it to NOT USE % Complete
  • I need it to flag at least 14 days prior to "End Date"
  • It needs to ignore BLANK dates
  • And it needs to capture only those line items with "In Progress" or "Not Started" Statuses


Update: I also used the following with no luck:

=IF(AND([End Date]@row < TODAY(), ISDATE([End Date]@row, OR(Status@row = "In Progress", OR(Status@row = "Not Started", 1, 0)))))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!