Date range formula

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?

Screen Shot 2018-02-13 at 6.47.03 PM.png

Find more posts tagged with

Project Management

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Automation not working
I have a Smartsheet automation setup to reminder the planning to complete their sections. It is setup to remind weekly until completed box is checked. They received 2 reminders and then the reminders stopped. Any idea what could be wrong? I can't tell that anything has changed in the sheet. Thanks Freda
How to disable popups that obscure my formula?
I'm editing formulas in a Sheet Summary. However, I'm prevented from seeing the formula by an annoying "helpful" popup that obscures my view. I can't figure out how to get rid of the popup. Does anyone know?
Workflow Error: "Recipients: Edit workflow to fix recipients list"
I am trying to create a workflow and for some reason it gives me an error with the email I entered as the recipient for my Alert. It is an email that I know works, it is shared to the sheet, and the access to the sheet is unrestricted. I am perplexed at what the issue is. Anyone else get this and know what the issue is?