If/And statements
Hi All,
So, I am trying to set a trigger flag to be checked when a request is within a certain amount of days and not approved or denied. I would like the flag to trigger a reminder email to the approver indicating the request is imminent and they have yet to approve/deny it. I can't seem to get the IF/And function to work properly. Or if you guys know of a better way to go about this I am all ears! I have tried:
=IF(AND([Need by Date]@row> TODAY()+10), [TTL approval]@row, "Submitted"), 1, 0)
=IF(AND([Need by Date]@row + 10 < TODAY(), [TTL approval]@row, "Submitted"), 1, IF(AND([Need by Date]@row + 10 < TODAY(), [TTL approval]@row, "Approved", [TTL approval]@row, "Denied"), 0))
I am able to get the flag to check strictly with:
=IF([Need by Date]@row + 10 < TODAY(), 1, 0)
But I don't want to send the reminder if they have already completed their approval task.
I really appreciate any help with this!
Thank you!
Comments
-
Hey Sesha -
There are a few ways to get this to work - your first formula was fairly close - replace with -
=IF(AND([Need by Date]@row < TODAY() + 10, [TTL approval]@row = "Submitted"), 1, 0)
You could also use these approaches as well:
1. In your post I see 3 approval values - "Submitted", "Approved", "Denied" - with the flag to display when they have yet to approve/deny it - that leaves "Submitted". For that you could simply use the formula:
=IF([Need by Date]1 < TODAY() + 10, IF([TTL approval]1 = "Submitted", 1, 0))
or
2. There are other values in the approval flow - "In Progress" for example - you could use the following formula:
=IF([Need by Date]@row < TODAY() + 10, IF(OR([TTL approval]@row = "Submitted", [TTL approval]@row = "In Progress"), 1, 0))
See the screenshot.
Let me know how this works.
Sean
-
This worked perfectly! Thank you for your help
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!