Return Value if Between Dates

sjy

Hello!

I have a [DATE SUBMITTED] column and need to create a reference column that returns the fiscal year that date falls in. Something like this - 

=IF [DATE SUBMITTED] > 10/01/17 AND [DATE SUBMITTED] < 09/30/18 RETURN "FY18"

and

=IF [DATE SUBMITTED] > 10/01/18 AND [DATE SUBMITTED] < 09/30/19 RETURN "FY19"

I'm not sure if I should use IF AND or if SmartSheet has a BETWEEN option for date formulas.

Any and all formula suggestions would be appreciated : )  

 

Zachary Taylor

Hey there!

Smartsheet doesn't currently have a between operator, but this can be accomplished with a combination of the IF and AND functions.

This formula should accomplish what you're looking for: 

=IF(AND([DATE SUBMITTED]@row >= DATE(2017, 10, 1), [DATE SUBMITTED]@row <= DATE(2018, 9, 30)), "FY18", IF(AND([DATE SUBMITTED]@row >= DATE(2018, 10, 1), [DATE SUBMITTED]@row <= DATE(2019, 9, 30)), "FY19", "")

Note the Greater/Less than or equal to operators (>=, <=) to ensure that dates that fall on the specified dates are included in the criteria.

I hope this helps!