Thursday, October 18, 2012

yogi_Count Number Of Times Specified Month Falls Between Dates In Columns A and B

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #815  Oct 19, 2012

user contacts-fvc said: (!category-topic/docs/spreadsheets/8bCq16TlISY)
Count a certain month in between two date columns. 
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
following is a solution to a bit more generalized problem that the month of interest can be specified (in cell D4)