count months looking at a list of dates

psrs0810

Well-known Member
Joined
Apr 14, 2009
Messages
1,109
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

<tbody>
</tbody><colgroup><col></colgroup>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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
 
Upvote 0
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

<tbody>
</tbody><colgroup><col></colgroup>
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top