Formula

jenmiller

I'm trying to build a formula.

 

Date range based off Finish date. 

 

Currently I have a date in the Finish date cell and it will populate a text based off the finish date but I really want to do a date range now based off the finish date.  Example below the date range would be 02.01.16 thru 09.30.16.

 

Can someone help me?

 

Current formula/ Example:

=IF(DATEONLY(Finish3)=DATE(2016,9,30), "pre-conversion"

Travis

Hi jenmiller! If you want to show a text value if a referenced date is in a range, use the AND function to check for dates between two given dates. We can do this by modifying your formula slightly. 

 

Try this:

 

=IF(AND(DATEONLY(Finish3) < DATE(2016, 9, 30), DATEONLY(Finish3) > DATE(2016, 2, 1)), "pre-conversion")

 

Hope this helps! 

jenmiller

This did help.  But I guess I should of told you the full story on what I'm trying to accomplish.  We have 6 phases to our project with different deadlines (Finish Date).

So I would like to build a formula for between dates based off the following dates:

 

Pre-merger (before Jan 15)

At merger (Jan 16 - Jan 31)

Phase 1 pre-conversion (Feb 1 - June 30)

Phase 2 pre-conversion (July 1 - Sept 30)

At conversion (Oct 1 - Oct 15)

Post conversion (Oct 16 or later)

 

When I put together the formula I keep getting a error message I beleive because of the dates seq.   But I can't figure it out..... 

 

Copy of formula not working:

=IF(DATEONLY(Finish3) <= DATE(2016, 1, 15), "pre-merger", =IF(AND(DATEONLY(Finish3) < DATE(2016, 1, 16), DATEONLY(Finish3) > DATE(2016, 1, 31)), "At Merger"), =IF(AND(DATEONLY(Finish3) < DATE(2016, 2, 1), DATEONLY(Finish3) > DATE(2016, 6, 30)), "Pre-conversion Phase 1"), =IF(AND(DATEONLY(Finish3) < DATE(2016, 7, 1), DATEONLY(Finish3) > DATE(2016, 9, 30)), "Pre-conversion Phase 2"),=IF(AND(DATEONLY(Finish3) < DATE(2016, 10, 1), DATEONLY(Finish3) > DATE(2016, 10, 15)), "At Conversion"),=IF(DATEONLY(Finish3) >= DATE(2016, 10, 16), "Post Conversion"))))))

 

My old formula Working WITHOUT between dates:

=IF(DATEONLY(Finish2) = DATE(2016, 9, 30), "pre-conversion", IF(DATEONLY(Finish2) <= DATE(2016, 1, 15), "pre-merger", IF(DATEONLY(Finish2) = DATE(2016, 1, 31), "at merger", IF(DATEONLY(Finish2) = DATE(2016, 10, 15), "at conversion", IF(DATEONLY(Finish2) >= DATE(2016, 10, 16), "post conversion", "Nothing")))))

 

Can you help?  My co-worker that is very good with excel tried to make it work and Smartsheet doesn't have all the same functions as excel so we couldn't figure it out.

 

Thanks

Jennifer

 

Travis

Hi Jennifer! A couple of things I notice...

 

First, in formulas, it is only necessary to add an = at the beginning of your formula. You do not need an = at the beginning of each function. 

 

Next, don't close the operation at the end of each function. Because this is a nested IF with multiple statements, we want to keep the operation open. 

 

Here is the updated formula. Notice I removed the extra = and the ) at the end of each function.

 

=IF(DATEONLY(Finish3) <= DATE(2016, 1, 15), "pre-merger", IF(AND(DATEONLY(Finish3) < DATE(2016, 1, 16), DATEONLY(Finish3) > DATE(2016, 1, 31)), "At Merger", IF(AND(DATEONLY(Finish3) < DATE(2016, 2, 1), DATEONLY(Finish3) > DATE(2016, 6, 30)), "Pre-conversion Phase 1", IF(AND(DATEONLY(Finish3) < DATE(2016, 7, 1), DATEONLY(Finish3) > DATE(2016, 9, 30)), "Pre-conversion Phase 2", IF(AND(DATEONLY(Finish3) < DATE(2016, 10, 1), DATEONLY(Finish3) > DATE(2016, 10, 15)), "At Conversion", IF(DATEONLY(Finish3) >= DATE(2016, 10, 16), "Post Conversion"))))))

jenmiller

Travis, thank you so much for your help.  But the formula still not working.  Do you think someone at Smartsheet would look at the issue?  Or can I share my example sheet with you?

Travis

Jennifier - I am someone at Smartsheet 

 

I have the exact formula I posted in my sheet and it is working. I used your column names, so you can copy and paste the formula I posted, into your sheet. 

 

If it is not working, what is the error you are getting? 

 

FYI: when you copy and paste text from the Community, extra text is added at the end of the pasted result. Make sure you delete the extra text when pasting the formula. 

 

If you are still unable to get it to work, share the sheet with me as an Admin and I can take a look: travis.hannon@smartsheet.com

jenmiller

I'm sharing the sheet with you.  I'm not getting a error (it's just blank) but it's not populating the text correctly based off the finish date.  Only two parts of the fomula is working pre-merger and post conversion.  Thank you for all your help & great to know I'm working with the best a smartsheet employee.

Travis

Thanks for sharing your sheet!

 

The < & > symbols in your formula are backwards. Lets take a look:

 

The first part of your formula:

 

IF(DATEONLY(Finish4) <= DATE(2016, 1, 15)

 

If Finish4 is less than or equal to 1/15/16. This works great - the condition is true if the referenced date is earlier than 1/15/16. 

 

The next part:

 

IF(AND(DATEONLY(Finish4) < DATE(2016, 1, 16), DATEONLY(Finish4) > DATE(2016, 1, 31))

 

This says, if Finish3 is less than 1/16/16 AND is greater than 1/31/16. This does not work - because it is saying, if the date is earlier than 1/16/16 and later than 1/31/16 (which isnt possible). 

 

This is why you are getting blank results. Reverse your symbols, like this:

 

IF(AND(DATEONLY(Finish4) > DATE(2016, 1, 16), DATEONLY(Finish4) < DATE(2016, 1, 31))

 

Also, if there is not a date in the Finish cell, you will get an error. Add the following to the front of your formula, which will first check for a date and will be blank if there is not one.

 

=IF(ISDATE(Finish4), 

jenmiller

OMG, thank you...  It's working!  Have a wonderful weekend......