Comparing dates IF/AND

Hi, I am trying to create a formula for:

If Date1 is less than 2/29/2022, then cost for the month is Feb2022 at row value. If Date1 is greater than 2/29/2022 and less than 3/31/2022, then cost for the month is Mar2022 at row value.

Formula below isn't working: any help on what I am doing incorrectly is much appreciated.

=IF([Date1]@row < "2/29/22", [Feb2022]@row, IF(AND([Date1]@row < "3/31/22", [Date1]@row > "2/29/2022", [Mar2022]@row), 0)

Thank you,




  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭


    Due to the various date formats Smartsheet can employ, when using a specific date as criteria, it only really works to use the DATE function. The format is DATE(YYYY, MM, DD). You're also missing an end parentheses to close out your AND statement (I added it in bold below.)

    =IF([Date1]@row < DATE(2022, 2, 29), [Feb2022]@row, IF(AND([Date1]@row < DATE(2022, 3, 31), [Date1]@row > DATE(2022, 2, 29)), [Mar2022]@row), 0)


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭

    I created a table which I use as a "Core" Table which has every month that helps with a bunch of things

    I then do a VLOOKUP on the month number and return the Month

    Something like this should work:

    =VLOOKUP(VALUE(MONTH([Date 1]@row)), {Months_Name}, 2, false) + YEAR([Date 1]@row)

    Where the {Months_Name} is the link to the other table

    That should just populate the column with something like Mar2022 or Jun2020.. no need for an IF

    I have run across issues with hidden characters causing a line return in some formulas.. if that happens use this one.. it should remove any line returns

    =SUBSTITUTE(VLOOKUP(VALUE(MONTH([Date 1]@row)), {Months_Name}, 2, false) + YEAR([Date 1]@row), CHAR(10), "")

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!