Problem with multiple date testings in same formula

[codys21]

I am trying to make a formula that outputs the first of the month following six months. This is pretty easy until I get an end date that is less than the first of the month following six months. I'll show an example of my spreadsheet and hopefully this will be easier to understand.

	Start Date	End Date	Years	Months	Days
Assignment 1	11/3/2004	5/4/2005	0	6	2
Assignment 2	1/15/2006	3/16/2006	0	2	2
Assignment 3	3/15/2007	6/15/2007	0	3	1
Assignment 4	2/15/2009	10/15/2009	0	8	1

<colgroup><col><col span="4"><col></colgroup><tbody>
</tbody>

So, my formula is giving me 6/1/2005 because 5/4/2005 is at least six months greater than 11/3/2004 but since 6/1/2005 is greater than 5/4/2005, I do not want this output. What I would like is to output 9/1/2009 because it is at least six months greater than 2/15/2009 and also falls before 10/15/2009.

Right now I have this giving me the start date if there is at least six months until the end date:

=IF(ISBLANK($C$9),$B$9,IF($C$9>EDATE($B$9,6),$B$9,IF(ISBLANK($C$10),IF(ISBLANK($B$10),"Not Eligible",$B$10),IF($C$10>EDATE($B$10,6),$B$10,IF(ISBLANK($C$11),IF(ISBLANK($B$11),"Not Eligible",$B$11),IF($C$11>EDATE($B$11,6),$B$11,IF(ISBLANK($C$12),IF(ISBLANK($B$12),"Not Eligible",$B$12),IF($C$12>EDATE($B$12,6),$B$12,IF(ISBLANK($C$13),IF(ISBLANK($B$13),"Not Eligible",$B$13),IF($C$13>EDATE($B$13,6),$B$13,IF(ISBLANK($C$14),IF(ISBLANK($B$14),"Not Eligible",$B$14),IF($C$14>EDATE($B$14,6),$B$14,$B$14))))))))))))

and this taking that start date and giving me the first of the month following six months after the start date:

=IF($B$20="","",(((IF((DAY($B$20)=1),(DATE(YEAR((DATE(YEAR($B$20),MONTH($B$20),1))),MONTH((DATE(YEAR($B$20),MONTH($B$20),1)))+6,DAY((DATE(YEAR($B$20),MONTH($B$20),1))))),(DATE(YEAR((DATE(YEAR($B$20),MONTH($B$20),1))),MONTH((DATE(YEAR($B$20),MONTH($B$20),1)))+7,DAY((DATE(YEAR($B$20),MONTH($B$20),1))))))))))

So, like I mentioned earlier, this is giving me 6/1/2005 when instead I want 9/1/2009.

Any help would be greatly appreciated!
Thank you!
Cody

 

[StephenCrump]

I'm not totally clear what you're trying to do here, so this is a bit of a guess. Is it close?

B7: {=EOMONTH(INDEX(B2:B5,MATCH(TRUE,EOMONTH(B2:B5-1,6)+1<=C2:C5,))-1,6)+1} array-entered

Excel 2010

	A	B	C	D	E	F
1		Start Date	End Date	Years	Months	Days
2	Assignment 1	3 Nov 04	4 May 05	0	6	2
3	Assignment 2	15 Jan 06	16 Mar 06	0	2	2
4	Assignment 3	15 Mar 07	15 Jun 07	0	3	1
5	Assignment 4	15 Feb 09	15 Oct 09	0	8	1
6
7	Result	1 Sep 09
8
9		Start Date	End Date	Years	Months	Days
10	Assignment 1	3 Nov 04	4 May 05	0	6	2
11	Assignment 2	15 Jan 06	16 Sep 06	0	8	2
12	Assignment 3	15 Mar 07	15 Jun 07	0	3	1
13	Assignment 4	15 Feb 09	15 Oct 09	0	8	1
14
15	Result	1 Aug 06
16
17		Start Date	End Date	Years	Months	Days
18	Assignment 1	3 Nov 04	4 Jun 05	0	7	2
19	Assignment 2	15 Jan 06	16 Mar 06	0	2	2
20	Assignment 3	15 Mar 07	15 Jun 07	0	3	1
21	Assignment 4	15 Feb 09	15 Oct 09	0	8	1
22
23	Result	1 Jun 05

<tbody>
</tbody>

Sheet1​

 

[codys21]

I'm not totally clear what you're trying to do here, so this is a bit of a guess. Is it close?

B7: {=EOMONTH(INDEX(B2:B5,MATCH(TRUE,EOMONTH(B2:B5-1,6)+1<=C2:C5,))-1,6)+1} array-entered

Excel 2010

	A	B	C	D	E	F
1		Start Date	End Date	Years	Months	Days
2	Assignment 1	3 Nov 04	4 May 05	0	6	2
3	Assignment 2	15 Jan 06	16 Mar 06	0	2	2
4	Assignment 3	15 Mar 07	15 Jun 07	0	3	1
5	Assignment 4	15 Feb 09	15 Oct 09	0	8	1
6
7	Result	1 Sep 09
8
9		Start Date	End Date	Years	Months	Days
10	Assignment 1	3 Nov 04	4 May 05	0	6	2
11	Assignment 2	15 Jan 06	16 Sep 06	0	8	2
12	Assignment 3	15 Mar 07	15 Jun 07	0	3	1
13	Assignment 4	15 Feb 09	15 Oct 09	0	8	1
14
15	Result	1 Aug 06
16
17		Start Date	End Date	Years	Months	Days
18	Assignment 1	3 Nov 04	4 Jun 05	0	7	2
19	Assignment 2	15 Jan 06	16 Mar 06	0	2	2
20	Assignment 3	15 Mar 07	15 Jun 07	0	3	1
21	Assignment 4	15 Feb 09	15 Oct 09	0	8	1
22
23	Result	1 Jun 05

<tbody>
</tbody>

Sheet1​

That is exactly the result I want! Except I don't understand how you did it with the array, sorry.

 

[codys21]

That is exactly the result I want! Except I don't understand how you did it with the array, sorry.

I think I figured it out. That is perfect and very easy, thank you!

 

[StephenCrump]

I think I figured it out. That is perfect and very easy, thank you!

You're welcome.