IF statements and checkboxes

Hi there,

Currently I'm using this formula to check boxes in a column if the date a proposal is sent is in 2019.

IF(AND([Date Proposal Sent]534 > DATE(2018, 12, 31), [Date Proposal Sent]534 < DATE(2019, 12, 31))

Sometimes there is no date value for "Date Proposal Sent" and the cell says "In Pending". This causes my checkboxes to say "Invalid Operation". How can I revise my formula so that if the "Date Proposal Sent" is "In Pending" that the checkbox is simply not marked off?

Thanks in advance!


Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You can also use:

    =IFERROR(IF(YEAR([Date Proposal Sent]@row) = 2019, 1), 0)

    Basically we just use the YEAR function to pull the year from the date instead of having to specify a date range.

    =IF(YEAR([Date Proposal Sent]@row) = 2019, 1)

    Then to account for no date being present throwing an error, we just wrap it in an IFERROR statement to replace the error with a 0.

    =IFERROR(IF(YEAR([Date Proposal Sent]@row) = 2019, 1), 0)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!