Return value of 1 when the start date still running up to the end date or cancelled date

Hi,

1.) I'm having a problem in my file which is I want to return a value of 1 when the contract start date is still running up to the contract end date -- it will exclude the all dates and consider only the dates in between.

For example:
The start date (column I) is November 20, 2014 and the end date (column K) is March 4, 2015. In this example, the month-year: November 2014 up to March 2015 will have a corresponding value of 1 and 0 to the other dates.

September 2014	October 2014	November 2014	December 2014	January 2015	February 2015	March 2015	April 2015	May 2015
0	0	1	1	1	1	1	0	0

I used this formula which is I think is correct however I think this is too complicated. Is there anyway to simplify this code:

Please Login or Register  to view this content.

2.) The other problem is that I have a cancellation date (column M) -- date when the client cancelled the contract. This will be the new contract end date instead of the old contract end date.

For example:
The start date (column I) is November 20, 2014 and the end date (column K) is March 4, 2015 and there has a cancellation date (column M) of January 12, 2015. In this example, the month-year: November 2014 up to January 2015 will have a corresponding value of 1 instead of November 2014 up to March 2015.

September 2014	October 2014	November 2014	December 2014	January 2015	February 2015	March 2015	April 2015	May 2015
0	0	1	1	1	0	0	0	0

3.) If the cancellation date column doesn't have any value, the date that will be considered will be the contract end date.

For the 2nd and 3rd question, I'm having a hard time thinking of the formula that can be used.


**Kindly disregard any duplicates in the raw data for now.

Originally Posted by hemesh

=IFERROR((SUMPRODUCT(--(TEXT(ROW(INDIRECT(INDEX(data!$H$1:$H$100,MATCH(Sheet1!$A4,data!$C$1:$C$100,0))&":"&IF(INDEX(data!$L:$L,MATCH(Sheet1!$A4,data!$C$1:$C$100,0))>0,INDEX(data!$J$1:$J$100,MATCH(Sheet1!$A4,data!$C$1:$C$100,0)),INDEX(data!$L$1:$L$100,MATCH(Sheet1!$A4,data!$C$1:$C$100,0))))),"MMMM YYYY")=B$2))>0)*1,"")

Works like a charm!! Thanks so much Hemesh, I have a problem encountered again because I do have duplicates however the data is not really a duplicate just a different values. for example in the data on that file I have:

Account	Start Date	End Date	Cancellation Date
A. Osmena Realty Corporation	July 2014	July 2015	September 2014
A. Osmena Realty Corporation	November 2015	May 2016	(blank)

You will see in the 1st row of data that it was cancelled on September 2014 and in the next row it resumed again on November 2015 up to May 2016. This means that I need to have a formula that will give me this kind of table:

April 2014	May 2014	June 2014	July 2014	August 2014	September 2014	October 2014	November 2014	December 2014	January 2015	February 2015	March 2015	April 2015	May 2015	June 2015	July 2015	August 2015	September 2015	October 2015	November 2015	December 2015	January 2016	February 2016	March 2016	April 2016	May 2016	June 2016	July 2016
0	0	0	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	0	0

Would this be possible? if not, should I first clean the data and then give a clean data to you?

Thanks!

Originally Posted by hemesh

@ Dylan ! The thing you asking related with duplicates a work around can be you can have same dups on second sheet and then we can fetch same data! Is that ok if so then let me know

Yes it is okay! However, the duplicates may have more than 1 or 2. What can we do about it? Thanks so much!

Originally Posted by hemesh

FYI i kept only One duplicate initially to show you other Dups you can manage in your actual sheet.

This is an array formula and you need to copy paste the formula in your sheet then hold control and shift together and then hit enter to confirm it as an array formula instead of just enter
Once the formula is confirmed as an array in formula bar you can see curly braces {} surrounding your formula which we cannot type manually.

Hi Hemesh,

I see. last question, what if I have 2 or more duplicates? I'll just have the formula be in an array form? Or should the formula be expounded/elaborated for the 2 or more duplicates? If so, could you paste it here? the formula? I'll just do it on my actual sheet. I think that's more than enough help I need.


Thank you so very much!!

Thank you hemesh! You saved me a lot of work! Thank you very very very much!