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

Options
✭✭✭✭
edited 11/21/23

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:

• ✭✭✭✭✭✭
Options

Try this:

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

• ✭✭✭✭✭✭
Options

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, "")

• ✭✭✭✭
edited 11/22/23
Options

@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'.

• ✭✭✭✭✭✭
Options

Try this:

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

• ✭✭✭✭
edited 11/22/23
Options

@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!!

• ✭✭✭✭✭✭
Options

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))

• ✭✭✭✭✭✭
Options

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!