Google Sheets Charts – Create Chart with Events by Month from Dates

google sheetsgoogle-sheets-chartstime series

I have a spreadsheet on google drive with a series of dates in a column, each date representing a particular event.

1998-01-07
1998-01-10
1998-01-22
1998-01-23

If I have two or more events on the same date, that date appear that many times in the column

2000-02-18
2000-03-12
2000-03-12
2000-03-12
2000-03-20

I'm trying to create a column chart with number of events by month without success.

Best Answer

First you should prepare your data to be plotted.

One of many ways is to use QUERY.

Example:

=query(A1:A9,"select year(A),month(A)+1, count(A) group by year(A),month(A)+1 label year(A) 'Year', month(A)+1 'Month', count(A) 'Count'")

NOTE: month(A)+1 is used because 0 is January, 1 February and so on, but people use 1 for January, 2 for February and so on.

Result

Year  Month Count 
1998      1     4
2000      2     1
2000      3     4

Then you could create a chart like the following:

Related Topic