Date range formula

mmscott92

Having an issue with date range formula.

I am trying to accomplish returning values such as Q2 FY18. Q3 FY18, Q4 FY18 based on the date showing in the "Finish" column.  However, I am challenged with the proper format to enter the date range, and then multiple at that.

I initially used ...

=IF([Finish]2>=10/29/2017, [Finish]2<=1/27/2018), "Q2 FY18", "x")

but have tried multiple other formulas with no success.  What is the correct format of formula for this?

Mike Wilday

It looks like you need to add an AND statement in there. 

=IF(AND([Finish]2>=10/29/2017, [Finish]2<=1/27/2018), "Q2 FY18", "x")

But if that doesn't work you could also implement the Date Function

=IF(AND([Finish]2>= Date(2017,10,29), [Finish]2<= Date(2018, 01, 27), "Q2 FY18", "x")

Check out this post for more on the AND function

Check out this post for more on the Date Function

mmscott92

I wasn't able to get the first suggestion to work, but after a little playing around, the second line works fine except I had to add a closing parenthesis at the end of the 2nd logical expression and before the value_if_true.

=IF(AND([Finish]2>= Date(2017,10,29), [Finish]2<= Date(2018, 01, 27)), "Q2 FY18", "x")

 

Thanks, Mike!!

Mike Wilday

Oh yep! Good catch. Glad I could be of help.