Thanks:  0
Likes:  0

Thread: count months looking at a list of dates

1. 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

2. Re: count months looking at a list of dates

Hi,

Try this

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

3. Re: count months looking at a list of dates

Try

=DATEDIF(EOMONTH(MIN(A1:A19),-1),EOMONTH(MAX(A1:A19),0),"m")

4. Re: count months looking at a list of dates

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

5. Re: count months looking at a list of dates

Mike LH and Jonmo1 - both formulas worked perfectly - Thanks!

6. Re: count months looking at a list of dates

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.

7. Re: count months looking at a list of dates

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

8. 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

9. 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

10. Re: count months looking at a list of dates

Originally Posted by psrs0810
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.

User Tag List

Posting Permissions

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