Compare a Date column with a static date value and accordingly pass a Yes or No into a dropdown list



I am new to Smartsheets and trying to set up a formula in Smartsheet that compares the Hire Date (a column with Date type) with a static date of 29 September 2019 and accordingly passes 'No' when the date is older than 29 September 2019 and passes a 'Yes' when Hire Date is later than 29 sept 2019 into a column of dropdown list type. I am using the formula as given below but I receive the #INVALID OPERATION error. I am using this to check the eligibility for a benefit based on the hire date of an employee.

=IF([Hire Date]:[Hire Date] > DATE(2019, 9, 29), "NO", "YES")

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi @Sonal Dubey ,

    You're close! Try this:

    =IF([Hire Date]@row < DATE(2019, 9, 29), "NO", "YES")

    This translates to - if the hire date on this row is less than (earlier than) 29 September 2019, display No. Otherwise, display yes.

    Let me know if it works!