If/And statements

Shesha_K · 05/24/19

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!

sean59916 · 05/24/19

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

Shesha_K · 05/26/19

This worked perfectly! Thank you for your help

If/And statements

Comments

Help Article Resources

Categories