3

I have a column of dates, eg.

2017-01-20
2017-01-25
2017-01-27
2017-01-30
2017-02-03
...
2018-12-20

I would like to create a plot where the x-axis is a contiguous range of dates from start to end (ie. 2017-01-20 to 2018-12-20). The y-axis would be a count of how many events happened on that day.

In my case, the event will occur either 0 or 1 times on each day. The data represents the days of the year on which I exercised, and I want to visualize it.

How can I plot this in Google Sheets?

Improve this question

2 Answers 2

Reset to default
2

Here's a solution that requires creating the contiguous date range (the x-axis values) in a separate column.

The high-level idea is to create a column with every date from start to end, then create a column that indicates whether each date is within the list of days on which the event occurred.

  • Insert the list of dates on which the event happened into column A
  • Highlight Columns A and B, and select Format > Number > Date from the menu
  • Add the start date, and the day after the start date in the first two rows of column B
  • Select the first two rows of column B, and then drag the little blue dot in the right corner of the selection down to automatically create a date series.
  • Keep dragging down until the desired end date is reached

Note: the date format in these examples is M/DD/YYYY enter image description here

  • In the first row of column C, enter =COUNTIF(A:A, "="&$B1) and press Enter. Then select the first row of column C, and fill down by double clicking the little blue dot in the right corner of the selection.

fill formula

  • Select columns B and C, and then select Insert > Chart from the menu
  • Change the Chart type to Column chart
  • Double click the chart, and select Customize > Gridlines, select Apply to Vertical axis, and set Major gridline count to 2

enter image description here

Improve this answer
1
  • populate column A with dates
  • populate column B with logic =IF(C1="",0,1)
  • populate column C wit events
  • select dataset of A and B columns
  • go to Insert > Chart
  • then double click the chart and edit gridlines of vertical axis to major gridline count: 2

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.