Does CONTAINS work with TODAY()

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?

Answers

  • David Joyeuse
    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
    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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!