IF statement to update a Completed field - looking at multiple criteria
![Peggy Parchert](https://us.v-cdn.net/6031209/uploads/userpics/YYRDMWWFX83G/nC7HLGW82ACPT.jpg)
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
-
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.
Answers
-
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)
-
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.
-
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.
-
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.
-
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.
-
Thank you @Paul Newcome - appreciate the help.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!