Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: count months looking at a list of dates

  1. #1
    Board Regular
    Join Date
    Apr 2009
    Posts
    1,078
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default count months looking at a list of dates

    I need a formula that will tell me the number of month based on the list of dates:
    e.g.
    10/10/2014
    10/24/2014
    11/7/2014
    11/21/2014
    12/5/2014
    12/19/2014
    1/2/2015
    1/16/2015
    1/30/2015
    2/13/2015
    2/27/2015
    3/13/2015
    3/27/2015
    4/10/2015
    4/24/2015
    5/8/2015
    5/22/2015
    6/5/2015
    6/19/2015

    I want to know that I have 9 months
    Excel 2007 & 2010

  2. #2
    Board Regular Mike LH's Avatar
    Join Date
    Mar 2015
    Location
    Liverpool
    Posts
    566
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count months looking at a list of dates

    Hi,

    Try this


    =SUM(IF(FREQUENCY(MONTH(A1:A19),MONTH(A1:A19))>0,1))

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count months looking at a list of dates

    Try

    =DATEDIF(EOMONTH(MIN(A1:A19),-1),EOMONTH(MAX(A1:A19),0),"m")
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    New Member
    Join Date
    Apr 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count months looking at a list of dates

    =month(A1) will give you the month number from the date in A1

  5. #5
    Board Regular
    Join Date
    Apr 2009
    Posts
    1,078
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count months looking at a list of dates

    Mike LH and Jonmo1 - both formulas worked perfectly - Thanks!
    Excel 2007 & 2010

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count months looking at a list of dates

    Glad to help..

    Just FYI, they do return different results if the Dates span more than 12 months...

    Mikes is will return a maximum of 12 (any and all January dates count as 1 total)
    January 2015 and January 2014 will be counted as only 1 month.

    Mine will count January 2015 and January 2014 as 2 seperate months.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  7. #7
    Board Regular
    Join Date
    Apr 2009
    Posts
    1,078
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count months looking at a list of dates

    good point - I did not go past 12 months.
    Thank you for pointing that out
    Excel 2007 & 2010

  8. #8
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count months looking at a list of dates

    You're welcome..

    Another big difference is
    Mine only counts how many months exist between the earliest date and the latest date.
    It doesn't consider if the between months are actually represented in your list.
    Meaning:
    If you had dates in Jan Feb May Aug
    Mikes would return 4, while mine would be 8
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  9. #9
    Board Regular
    Join Date
    Apr 2009
    Posts
    1,078
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count months looking at a list of dates

    actually - dumb question. I changed around my dates and I was expecting to get a result of 10 months, but it is only showing 9

    =DATEDIF(EOMONTH(MIN(AE10:AE31),-1),EOMONTH(MAX(AE10:AE31),0),"m")
    9/5/2014
    9/19/2014
    10/3/2014
    10/17/2014
    10/31/2014
    11/14/2014
    11/28/2014
    12/12/2014
    12/26/2014
    1/9/2015
    1/23/2015
    2/6/2015
    2/20/2015
    3/6/2015
    3/20/2015
    4/3/2015
    4/17/2015
    5/1/2015
    5/15/2015
    5/29/2015
    6/12/2015
    6/26/2015
    Excel 2007 & 2010

  10. #10
    Board Regular Mike LH's Avatar
    Join Date
    Mar 2015
    Location
    Liverpool
    Posts
    566
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: count months looking at a list of dates

    Quote Originally Posted by psrs0810 View Post
    good point - I did not go past 12 months.
    Thank you for pointing that out

    Hi,

    Just to clarify my formula. It counts unique months so if all the dates where dates in January then it would return 1 or if half of the dates were January and the rest December then it would return 2. It now seems I may have misunderstood the requirement and provided the number you wanted purely by coincidence.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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