If/And statements

Shesha_K ✭✭✭
edited 12/09/19 in Formulas and Functions

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
    sean59916 ✭✭✭
    edited 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))


    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.




  • Shesha_K
    Shesha_K ✭✭✭

    This worked perfectly! Thank you for your help :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!