IF statement to update a Completed field - looking at multiple criteria

I'm trying to update a Completed column with a formula.

I need this formula to to look at the following columns:

CW Sent (checkbox column)

Event Date (date column)

Private Date (date column)

Online Date (date column)

Criteria:

1) If CW Sent is checked and Event Date is in the past or today, mark Completed

or

2) If CW Sent is checked and Event Date is blank, then it needs to look at:

2.1) If CW Sent is checked and Private Date is in the past or today, mark Completed

or

2.2) If CW Sent is checked and Online Date is in the past or today, mark Completed

I have this so far:

=IF(AND([CW Sent]@row = "true", [Event Date]@row <= TODAY()), 1, IF(OR([Private Start Date]@row <= TODAY(), [OnDemand Start Date]@row <= TODAY()), 1, 0))

It is not working - suggestions?

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!