Count unique months between date ranges

[Levigate]

I'm scrambling my head to put this together. Could somebody show me how this is done?
I have two rows of data that contains dates in the mmm-yy format. The first row is the start date of the activity and the second row is the end date of the activity. Thus, in each column i would have an activity with its start and end dates. If i want to total the number of months taking into consideration only unique months (counting over-lapping months just once), how do i do that? These dates could have breaks in between, too. Like if an activity ended Jul-08, the next could begin Dec-08.
I am able to count the months using the datedif function, is there a match or other function that i need to use?

Thanks!

 

[Colin Legg]

Yes, it assumes that the activities are in date order. Otherwise you would have to check for "gaps" or "overlaps" for each activity against all the other activities. There would be the same problem with the logic you were trying to use.

As for your formula returning 20, that is most likely due to a problem with the dates in your table. You are formatting them as mmm-yy, which means that the days are not showing. Check the days for each month to make sure they are all the same day of the month.

 

[barry houdini]

Bit unwieldly, but you could use this formula

=SUM(IF(MMULT((DATE(YEAR(MIN(C3:F3)),MONTH(MIN(C3:F3))+ROW(INDIRECT("1:"&DATEDIF(MIN(C3:F3),MAX(C4:F4),"m")+1))-1,1)>=C3:F3)*(DATE(YEAR(MIN(C3:F3)),MONTH(MIN(C3:F3))+ROW(INDIRECT("1:"&DATEDIF(MIN(C3:F3),MAX(C4:F4),"m")+1))-1,1)<=C4:F4),TRANSPOSE(COLUMN(C3:F3)^0)),1))

confirmed with CTRL+SHIFT+ENTER

That should allow the dates to be in any order....although there shouldn't be any blanks

 

[Levigate]

Thanks colin / Barry!
Both fantastic solutions work!

ray: You guys are great. And i am off to learn more about the MMULT function.

Cheers!