Google Spreadsheet Formula to calculate number of times a month occurs between two dates

Question:
 
( by contacts-fvc )

If I want to see how many times a certain month falls in between two dates (i.e.  count how many times November is in between the two columns [answer: 4]) how would I do that?

I have two columns like this:

Date Start Planned End

6/25/2012 9/11/2012

7/26/2012 2/7/2013

6/21/2012 10/11/2012

7/5/2012 12/19/2012

5/16/2012 1/2/2013

6/25/2012 12/11/2012

4/18/2012 10/3/2012

I've searched all excel and google spreadsheet forums and most of them can count them, but can't find a formula to get what I want.  I thought this formula would work: =ARRAYFORMULA(SUM((A2:A7>10/31/2012)*(B2:B7<12/1/2012))) but I don't know what I'm doing wrong. Please help! Thanks in advance

Solution:

Here is my formula to calculate the number of times November occurring between Column A and Column B:

=sum(ARRAYFORMULA(IFERROR(ROUNDDOWN(((month(B:B)-month(A:A))+((year(B:B)-year(A:A))*12))/12);"")+iferror(if((IFERROR(MOD(((month(B:B)-month(A:A))+((year(B:B)-year(A:A))*12));12);""))>0;if((month(A:A)+(IFERROR(MOD(((month(B:B)-month(A:A))+((year(B:B)-year(A:A))*12));12);"")))>=11;1;0);"");"")+iferror((month(A:A)=11)*(month(B:B)=11);"")))

The formula will work as, if we have date having month 11 in Column A or in Column B then the formula will include it in counting that too.

So if we have database as:

DateStart PlannedEnd

11/15/2012 12/18/2012

then the above formula will give the result as 1.

So, now I have made formula to include the month November even if it is running (current) month.

And if you want to see the counting of month in each corresponding row,

then put the following formula in first cell of some new column:

=ARRAYFORMULA(IFERROR(ROUNDDOWN(((month(B:B)-month(A:A))+((year(B:B)-year(A:A))*12))/12);"")+iferror(if((IFERROR(MOD(((month(B:B)-month(A:A))+((year(B:B)-year(A:A))*12));12);""))>0;if((month(A:A)+(IFERROR(MOD(((month(B:B)-month(A:A))+((year(B:B)-year(A:A))*12));12);"")))>=11;1;0);"");"")+iferror((month(A:A)=11)*(month(B:B)=11);""))

Following is the screenshot of my spreadsheet having some dates in Column A and Column B:

in above screenshot, you can see the Column C is having counts for each row. And Cell D8 is having the formula for total.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.in/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive