Does CONTAINS work with TODAY()

MakeItHappen

This formula is returning false even when TODAY() is in the value in the [Date1]@row cell.

=IF(CONTAINS(TODAY(), [Date1]@row:[Date3]@row), "true", "false")

The range [Date1]:[Date3] are formatted as a date columns.

Does CONTAINS work with dates?

David Joyeuse

Hi @MakeItHappen

CONTAINS looks for strings of characters, it could be not working because your date columns aren't formatted the same way as TODAY().

If TODAY() returns 11/01/2020, then CONTAINS will return false if [Date1]@row is 01/11/2020. Even if in both case that means November the 1st.

Have you tried to replace the TODAY() formula in your CONTAINS with the hardtyped date and see what happens?

MakeItHappen

Thanks @David Joyeuse @David Jazz ,

I ended up with the following formula to achieve the same result as CONTAINS. It is a lot more messy.

=IF(IFERROR(MATCH(TODAY(), [Date1]@row:[Date3]@row), 0) -1) > 0, "True", "False")

Logic:

• MATCH todays date in the range will give me the column position, i,e positive number
• If it is exist then the the positive number / column position will be ">0" and therefore True
• If is does not exist then the MATCH expression give me an error and the formula fails. So I used IFERROR to return -1. So evaluation the expression IF( -1>0) returns a FALSE