How to Plot Occurrences of Dates v/s Week Number in Google Sheets | 5 Easy Steps

Reading Time: 3 minutes
Final plot is ready

5 Easy Steps to Plot Occurrences of Dates v/s Week Number in Google Sheets

How to plot occurrences of dates v/s week number in Google Sheets

Split the Datetime string to Date and Time

  • Let’s say your dataset contains data in DateTime format, as shown below:

Figure 1: Dates in Datetime format
Figure 1: Dates in Datetime format
  • Use the SPLIT function to separate it to date and time, as shown below:

=SPLIT(A2,” “)

  • Apply the formula to all the cells of your dataset.
  • The output will be as shown below:

Figure 2: Dates split using the SPLIT function
Figure 2: Dates split using the SPLIT function

Use the WEEKNUM function to categorise dates to a week

  • The WEEKNUM function returns a number representing the week of the year where the provided date falls.
  • Use the function, as shown below:

=WEEKNUM(B2)

  • Apply the formula to all the cells of your dataset.
  • The output will be as shown below:

Figure 3: Week number allotted to all dates
Figure 3: Week number allotted to all dates

Create a PIVOT Table

  • Before creating a pivot table, ensure that the rows in focus are adjacent to each other, as shown below:

Registration dates and Week number in Google Sheets
Figure 4: Registration dates and Week number in Google Sheets
  • Select columns Week and Registration Created At.
  • Go to Insert → Pivot Table.

Figure 5: Create PIVOT table
Figure 5: Create PIVOT table
  • Verify the data range and click Create.
  • A PIVOT table will be created on a new sheet.

Figure 6: Create PIVOT table
Figure 6: Create PIVOT table

Select Columns and Values of the PIVOT table

  • Go to the Columns option in the Pivot table editor.
  • Select Week Number.
  • Unselect Show Totals.
  • Go to the Values option in the Pivot table editor.
  • Select Registration Created At.
  • Summarise values by COUNTA as shown below:

Figure 7: Insert parameters
Figure 7: Insert parameters

Insert Chart to visualise the trend of occurrence of Dates against Week number in Google Sheets

  • Select the entire pivot table which is created.
  • Go to Insert → Chart.

Figure 8: Insert Chart
Figure 8: Insert Chart
  • A bar graph is created.
  • Go to Chart Editor.
  • Go to Setup.
  • Select the option Switch rows/columns, as shown below:

Figure 9: Switch rows and columns
Figure 9: Switch rows and columns
  • Remove the A2:M2 range from the Series tab, as shown below:

Figure 10: Remove extra rows
Figure 10: Remove extra rows from the plot
  • Add label to identify week number against occurrences of that particular week, as shown below:

Figure 11: Label X-axis
Figure 11: Label X-axis of the plot
  • The final output will be a bar graph representing the trend of occurrences of registration dates of a particular week across months.

Figure 12: Final plot is ready
Figure 12: Example of how to Plot Occurrences of Dates v/s Week Number in Google Sheets.

See Also

Most Commonly Used Google Script Functions for 2022: Learn about widely used advanced script functions.

shalinamigo

←
→

Leave a Reply