IF formula for date and text in the same cell.

edited 05/23/22 in Smartsheet Basics


I currently have this formula for a cell:

=IF(AND([Quote Validity]62 < TODAY(), ISDATE([Quote Validity]62)), "EXPIRED")

However, if I need to put anything other than a date for Quote Validity cell. It returns an "#INVALID OPERATION"

What needs to be added to that formula so when a text, besides a date is inputted, it doesn't input anything (cell is just blank).


Best Answer

  • Matt Johnson
    Matt Johnson ✭✭✭✭✭✭
    Answer ✓

    Hi @SmartsheetRookie

    I tested it a little further and if your "Quote Validity" column is a date column I think it works as expected. You can still enter text into that column as long as you leave the "Restrict to Dates" box unchecked.

    • If the date is in the past, show EXPIRED
    • If the date is in the future, show nothing
    • If the date is text, show nothing.

    Also, if I'm understanding this correctly, you don't actually need the ISDATE part of the formula. The below formula is doing the same thing.

    =IFERROR(IF([Quote Validity]@row < TODAY(), "EXPIRED"), "")

    I hope that helps.


    Matt Johnson

    Sevan Technology

    Smartsheet Aligned Partner