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    www.energyefficientbuild.com.

user contacts-fvc said: (http://productforums.google.com/forum/?zx=9jrp4o21m1am#!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)


3 comments:

  1. That's good :)

    My formula is looking a bit ugly,
    And I think there would be much more better way of calculating this, but I also think we have already invested a lot of our time on this and now let us leave it as it is.

    Kishan.
    iGoogleDrive
    :)

    ReplyDelete
  2. Hi Kishan:

    Thanks for your comment ... in any event, the formula I have presented is the best i can do as of now -- if I find a better solution, I will post that here in my blog.

    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete