Comparing dates IF/AND

dsmartsheetuser

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,

Deepthi

Jeff Reisman

@dsmartsheetuser

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)

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), "")