Creating bar graph of event occurrences for each month of several years in excel

Question

I have a long list of dates (about 2000) in an excel spreadsheet and I want to see how many of these dates lie within particular years (and roughly which part of the year also if possible). I know how to make basic graphs in excel, but I'm not sure how to make a bar graph that uses dates. Any suggestions?

I can obviously split events in day, month, and year and then use count to count the amount of each year, but this would not show the placement of the event within the year in the final graph.

20/03/2000
2/04/2000
3/04/2000
26/05/2000
7/06/2000
20/06/2000
22/06/2000
10/07/2000
12/07/2000
22/07/2000
2/08/2000
8/08/2000
11/08/2000
14/08/2000
15/08/2000
12/09/2000
15/09/2000
20/09/2000
25/09/2000
2/10/2000
22/10/2000
24/10/2000
25/10/2000
27/10/2000
1/11/2000
10/11/2000
13/11/2000
16/11/2000
18/11/2000
20/11/2000
25/11/2000
27/11/2000
3/12/2000
6/12/2000
20/12/2000
21/12/2000
22/12/2000
4/01/2001
7/01/2001
11/01/2001
24/01/2001
25/01/2001
2/02/2001
4/02/2001
9/02/2001
12/02/2001
13/02/2001
20/02/2001
21/02/2001
2/03/2001
11/04/2001
19/04/2001
20/04/2001
21/04/2001
24/04/2001
27/04/2001
28/04/2001
2/05/2001
3/05/2001
5/05/2001
12/05/2001
13/06/2001
20/06/2001
25/06/2001
3/07/2001
5/07/2001
18/07/2001
20/07/2001
21/07/2001
22/07/2001
25/07/2001
4/08/2001
5/08/2001
9/08/2001
10/08/2001
11/08/2001
12/08/2001
13/08/2001
31/08/2001
11/09/2001
12/09/2001
17/09/2001
3/10/2001
10/10/2001
18/10/2001
21/10/2001
23/10/2001
4/11/2001
5/11/2001
17/11/2001
22/11/2001
23/11/2001
27/11/2001
29/11/2001
3/12/2001

Answer 1

I cannot check this in earlier versions of Excel, but in Excel 2016, if you build a Pivot Table with your data and drop the date in the Rows - you get your data grouped in Years, Quarters, and Months. If you also drop the date into Values, then you get the Count of values in each year, quarter, and month.

Then a pivot chart looks like this ...

---

A less magical way to do it

Applies to Excel 2013 and up

1. From your single column of dates, build a pivot table.
2. Put the date in Rows. Put Count of date in Values.
3. Select any one of the dates. Right Click and choose "Group...", or choose "Group Selection" from the Analyze ribbon.
4. Click on each of Months, Quarters, and Years.
5. Filter how you would like.
6. Select PivotChart from the Analyze Ribbon.

Answer 2

You can create a scatter graph as suggested in the comment. Consider your data set and see if there is any other quality that could be added to the chart, which could be plotted on the value axis. For example the number of people attending the event, or the duration of the event in seconds, or something like that, so that the graph contains more information. In the following screenshot, the blue dots are just the 1's from column B plotted on the time line. The orange dots plot another data aspect on the vertical axis.