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

Peggy Parchert

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?

Paul Newcome

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.

Paul Newcome

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)

Peggy Parchert

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.

Paul Newcome

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.

Peggy Parchert

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.

criteria.png

Paul Newcome

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.

Peggy Parchert

Thank you @Paul Newcome - appreciate the help.

Paul Newcome

Happy to help. 👍️