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

Options
✭✭✭✭✭✭

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?

• ✭✭✭✭✭✭
Options

Based on that screenshot, the formula would be:

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

Which really only removes the criteria of the [Event Date] not being blank.

• ✭✭✭✭✭✭
Options

Try something like this...

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

• ✭✭✭✭✭✭
Options

Thank @Paul Newcome - it works sort of. It is checking Completed if CW Sent is checked and Event Date is in the future - how do I stop that? It is possible that CW has been sent but the Event Date hasn't passed yet.

• ✭✭✭✭✭✭
Options

Double check that the other dates are not in the past. In your post you said that as long as the CW is checked and one of the other two dates is in the past then mark complete.

• ✭✭✭✭✭✭
Options

I've attached a screenshot of the criteria. I should have done this first - someone in Community even recommended it for a visual in one of my other questions to help with working out formulas. Apologize. Please see attachment.

• ✭✭✭✭✭✭
Options

Based on that screenshot, the formula would be:

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

Which really only removes the criteria of the [Event Date] not being blank.

• ✭✭✭✭✭✭
Options

Thank you @Paul Newcome - appreciate the help.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!