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

Tana V

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

Paul Newcome

Try this:

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

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

@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

Try this:

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

Tana V

@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

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

Paul Newcome