Formula help for date <90 days and text "includes"
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 (autogenerated) 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, "")
Best Answer

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

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

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

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

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

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))
Help Article Resources
Categories
Check out the Formula Handbook template!