+ Reply to Thread
Results 1 to 6 of 6

How to Count Months in a Column of Dates

  1. #1
    Registered User
    Join Date
    01-30-2006
    Posts
    10

    Smile How to Count Months in a Column of Dates

    Hello all,

    Hope you can assist with a slight problem I've encountered.

    I have a large column of date entries (several columns, actually, but same applies to all of them).

    This column is a Contact Date (and has been named so it can be accessed by reference as 'contact'), recording when we first interacted with an individual. It contains values up to ten or so years ago.

    There is a report associated with this data, where I need to break down, by month, when people contacted us. This information is only relevant however to people contacting us in the current financial year.

    At the moment, I have to take the date of contact, pass it through an AND to see if it falls into the correct year. This obviously leads to a column of TRUE/FALSEs 1-to-1 related to the contact dates. If it is TRUE, then I take the Month() value from the contact date. Using a Countif across the column of determined months is used in the report (for example,
    =countif(contactResults,3)
    returns the value for people first seen in March).

    I was thinking a VBA script would be able to compress this to a single function, except I do not know how to address a column for input purposes. I'm aiming for something akin to the following:


    Function newContacts(inputSet As [?], selectedMonth As Integer)
    newContacts=0
    Do
    if ((inputSet[element]>=(1st date of fin.year)) AND
    (inputSet[element]<=(last date of fin.year)))
    then
    if month(inputSet[element]) = selectedMonth
    then
    newContacts = newContacts +1
    end if
    end if
    Loop Until [end of inputSet]
    End Function


    such that a call to newContacts(contact,3) should return the same as the nested functions described above. Alternatively, referring into the function by newContacts(N:N,3) would work well enough (although be less clear to other users).

    Is this possible?

    Many thanks in advance.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Don't know about VBA but you could count the number of dates which fall within a specific month and year using a single formula, e.g. for April 2006

    =SUMPRODUCT(--(MONTH(contactResults)=4),--(YEAR(contactResults)=2006))

    where contactResults is the range containing your dates
    Last edited by daddylonglegs; 01-30-2006 at 09:42 AM.

  3. #3
    Tom Ogilvy
    Guest

    Re: How to Count Months in a Column of Dates

    You could actually get your entire table built in one step by creating a
    Pivot Table. Group on Year and Month

    or

    Using the Countif Formula you have already shown you can get the results
    directly with no intermediate columns:

    =Countif(A:A,">=3/1/2004")-countif(A:A,">3/21/2004")

    will give you the count for March 2004. this will also calculate much
    faster than the sumproduct formula suggested.

    --
    Regards,
    Tom Ogilvy



    "Dexsquab" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hello all,
    >
    > Hope you can assist with a slight problem I've encountered.
    >
    > I have a large column of date entries (several columns, actually, but
    > same applies to all of them).
    >
    > This column is a Contact Date (and has been named so it can be accessed
    > by reference as 'contact'), recording when we first interacted with an
    > individual. It contains values up to ten or so years ago.
    >
    > There is a report associated with this data, where I need to break
    > down, by month, when people contacted us. This information is only
    > relevant however to people contacting us in the current financial
    > year.
    >
    > At the moment, I have to take the date of contact, pass it through an
    > AND to see if it falls into the correct year. This obviously leads to
    > a column of TRUE/FALSEs 1-to-1 related to the contact dates. If it is
    > TRUE, then I take the Month() value from the contact date. Using a
    > Countif across the column of determined months is used in the report
    > (for example,
    > =countif(contactResults,3)
    > returns the value for people first seen in March).
    >
    > I was thinking a VBA script would be able to compress this to a single
    > function, except I do not know how to address a column for input
    > purposes. I'm aiming for something akin to the following:
    >
    >
    > Function newContacts(inputSet As [?], selectedMonth As Integer)
    > newContacts=0
    > Do
    > if ((inputSet[element]>=(1st date of fin.year)) AND
    > (inputSet[element]<=(last date of fin.year)))
    > then
    > if month(inputSet[element]) = selectedMonth
    > then
    > newContacts = newContacts +1
    > end if
    > end if
    > Loop Until [end of inputSet]
    > End Function
    >
    >
    > such that a call to newContacts(contact,3) should return the same as
    > the nested functions described above. Alternatively, referring into
    > the function by newContacts(N:N,3) would work well enough (although be
    > less clear to other users).
    >
    > Is this possible?
    >
    > Many thanks in advance.
    >
    >
    > --
    > Dexsquab
    > ------------------------------------------------------------------------
    > Dexsquab's Profile:

    http://www.excelforum.com/member.php...o&userid=30966
    > View this thread: http://www.excelforum.com/showthread...hreadid=506323
    >




  4. #4
    Registered User
    Join Date
    01-30-2006
    Posts
    10
    Sumproduct seems to be for multiplying two or more equally sized arrays.

    I've tried a few combinations of the line you've suggested, and havent been able to make it work.

    I only have one array (a column) that needs to be searched and summed across. To give a more concrete example:

    [contact]
    03/01/2001 FALSE
    01/07/2001 FALSE
    19/06/2004 FALSE
    22/08/2004 FALSE
    31/03/2005 FALSE
    01/04/2005 TRUE 4
    18/04/2005 TRUE 4
    22/06/2005 TRUE 6
    31/03/2006 TRUE 3
    01/04/2006 FALSE
    27/08/2006 FALSE

    -The first column (labelled contact) is a list of dates.

    -2nd column is a boolean generated by =AND($A2>38442, $A2<38808)
    (used numbers as using other date references seemed to throw it out)

    -3rd column is the month of contact, if it falls in the right year; generated by
    =IF(AND($A2>38442, $A2<38808), MONTH($A2), "") (as you can see, the column 2 calculation is embedded in this function)

    It is this list of numbers that becomes contactResults, such that countif(contactResults,3) would return all the valid March contacts.

    I seek a single function to look through the dates and find (for example) only the March results in the year 2005.

  5. #5
    Registered User
    Join Date
    01-30-2006
    Posts
    10
    Thanks Tom, it's looking like the combination of two countifs will work.

    Trying it out now.

    Many thanks, again.

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Hi Dexsquab,

    I'm sure Tom's right about COUNTIF being the better option.

    Just for your information.....

    Yes, the SUMPRODUCT formula can multiply equally sized arrays, it works on the dates directly, so if your dates are in A1:A100 it would simply be

    =SUMPRODUCT(--(MONTH(A1:A100)=4),--(YEAR(A1:A100)=2006))

    the two criteria in the formula produce equally sized arrays of TRUE/FALSE values which are then coerced to 1/0 values and can therefore be multiplied and added, this effectively gives you a count of when both conditions are true.

    You could even shorten it to

    =SUMPRODUCT(--(TEXT(A1:A100,"mmm-yy")="Apr-06"))

    note: you can't use whole column references like A:A with SUMPRODUCT
    Last edited by daddylonglegs; 01-30-2006 at 03:36 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1