Most Common Month within list of Dates

Hello, I would like to use a formula that returns the month in which the most date occurrences from a list falls within. For example:

Data:

1/1/16
1/22/16
1/24/16
1/24/16
2/3/16
3/4/16
3/4/15
4/4/15

Formula would return: 1/1/16 or January 2016.

Thanks for the help!

Try this...

Data Range

	A	B	C
1	Date	------	Most Common
2	1/1/2016		January 2016
3	1/22/2016
4	1/24/2016
5	1/24/2016
6	2/3/2016
7	3/4/2016
8	3/4/2015
9	4/4/2015

This array formula** entered in C2:

=TEXT(MODE(A2:A9-DAY(A2:A9)+1),"mmmm yyyy")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

The simplest way would be to create a Pivot Table with the dates in both the Row Labels and Values areas. Group the row dates by Month and set the values to Count. Then if necessary sort the PT by Count of date in descending order.

...or a non array formula

Formula:

Please Login or Register  to view this content.

Originally Posted by Richard Buttrey

...or a non array formula
=CHOOSE(MODE(MONTH(A1:A20)),"January","February","March","April","May",
"June","July","August","September","October","November","December")

The dates aren't all within the same year.

@Tony - good point. Missed that.

...

OTOH the OP was only asking for the month with the highest number of dates, not the month in a particular year with the highest number of dates.

Maybe the question needs clarifying.

I assumed that's why they included the year in their expected result:

Formula would return: 1/1/16 or January 2016.

You're welcome. Thanks for the feedback!