Date series in Google Sheets

When visualizing data in Google Sheets, one encounters the problem that the \(x\) axis cannot automatically fill in the missing gaps for text values (such as calendar weeks). To solve the problem, one generates a continuous date series, counts the occurring cells themselves and then visualizes the cumulated totals.


The standard diagram, which counts the number of occurring values, has the mentioned gaps (in the example the calendar week "24/2020" is missing):

If you want to generate a date series (between a fixed start date and a day relative to today's date), you can use ARRAYFORMULA and LINE :

9d60612e712ab8d9ea16e760161ff89b

The same can also be done for calendar weeks (and, if required, for quarters and years):

9d60612e712ab8d9ea16e760161ff89b

Cell \(F1\) the start date and \(F2\) the number of days relative to today's day. Now we count the different values ​​of the continuous date series:

9d60612e712ab8d9ea16e760161ff89b

This allows the above diagram to be displayed without any gaps:

Back