(Didnt realize I could add a spreadhseet to the post, See Attached) I am trying to come up with a formula that will count the number of months in the year that the license for a program has and I am getting stuck. Ex:
Title Start Date End Date # Mos 2010 2011 2012 2013
XXX 7/1/10 12/31/12 30 6 12 12 0
YYY 11/1/10 4/30/13 30 2 12 12 4
ZZZ 2/1/11 7/31/13 30 0 11 12 7
currently, I am using the following formula for each cell under the 2010, 2011, 2012, and 2013 columns, but it isnt working because the datedif function is adding extra months for title ZZZ in 2013:
=IF(ISERROR(IF($G10>=J$1,DATEDIF($G10,J$2,"m")+1,IF(J$2<=$H10,DATEDIF(J$1,J$2,"m"),DATEDIF(J$1,$H10,"m")+1))),0,IF($G10>=J$1,DATEDIF($G10,J$2,"m")+1,IF(J$2<=$H10,DATEDIF(J$1,J$2,"m"),DATEDIF(J$1,$H10,"m")+1)))
If someone can come up with an easier solution/formula, i would appreciate it. Thanks!
Bookmarks