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.
<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
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