Formula help for date <90 days and text "includes"

Tana V
Tana V ✭✭✭✭
edited 11/21/23 in Formulas and Functions

So really stumped on this one, my formula is working with the exception of the text including "Test".

I need to have a star (1) put into this cell IF:

Column [Expiration Date] is <= 90 days from today AND another column (Client) includes the word "Test". My current formula (auto-generated) is not working, any help is much appreciated!!

My current formula:

IF(AND(NOT(ISBLANK([Expiration date]@row)), [Expiration Date]@row - TODAY() <= 90, Client@row <> "Test"), 1, "")

Tags:

Best Answer

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    If I understand correctly, your logic is backwards in regard to including "Test". Your current formula is looking for rows that do NOT contain "Test". To include rows that DO include test:

    IF(AND(NOT(ISBLANK([Expiration date]@row)), [Expiration Date]@row - TODAY() <= 90, Client@row = "Test"), 1, "")

  • Tana V
    Tana V ✭✭✭✭
    edited 11/22/23

    @Carson Penticuff thank you! Will the Client@row = "Test" get everything that "includes" Test so if it is "This is a Test" it will include that or does it have to = Test (and nothing else) in the cell? Hope that makes sense. Trying it now but so far not getting it to accept the formula.

    Thanks again!

    I think what I am missing is the = has to be exact and what I need is 'includes'.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this:

    =IF([Expiration Date]@row <> "", IF(AND([Expiration Date]@row<= TODAY(90), CONTAINS("Test", Client@row)), 1))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Tana V
    Tana V ✭✭✭✭
    edited 11/22/23

    @Paul Newcome thanks! That worked! Can you tell me how to copy this formula to do the exact opposite? Where the Client@row does NOT contain "Test"?

    I think I got it - -

    =IF([Expiration Date]@row <> "", IF(AND([Expiration Date]@row <= TODAY(90), NOT(CONTAINS("Test", Client@row)), 1))

    Thanks again!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would simply wrap the CONTAINS piece in a NOT function.

    =IF([Expiration Date]@row <> "", IF(AND([Expiration Date]@row<= TODAY(90), NOT(CONTAINS("Test", Client@row))), 1))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭


    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!