Create Charts with Dates or Time – Excel & Google Sheets

This tutorial will demonstrate how to create charts with dates and times in Excel & Google Sheets

Create Charts with Dates or Time – Excel

Starting with Data

In this scenario, we want to show a graph showing the date and/or time on the X Axis and the sales on the Y Axis.

Data for Time Date Chart in Excel

 

Create Scatterplot

  1. Highlight Data
  2. Click Insert
  3. Select Scatterplot Graphs
  4. Select Scatter

Create Scatterplot Graph in Excel Time Date

 

Beginning Scatterplot

We get a scatterplot like below, but first we want to format some aspects of the graph

Beginning Scatterplot Graph in Excel Time Date

 

Understanding X Axis

Next, we want to understand what date and time we want on the X Axis. For this scenario, we want a tick every two hours, for a total of 12 ticks on the X Axis. Repeat this with the same information in the second column as well.

X Axis Table Graph Chart for Time and Date in Excel

 

Create X Axis Table

  1. Highlight the second column
  2. Change format to General

Format Table for Date and Time in Excel

 

3. Add another Column to find the difference between the two values. This will help understand how much space to leave between ticks.

Axis Bounds for Min and Max for Time Date Graph in Excel

 

4. Change the format to General

Format X Axis for Date Time Graph in Excel

 

Final X Axis Table

The following table should look like the one below. This will help us to insert the parameters of the chart to show exactly how we would like to see it.

Final X Axis Table for Date Time Graph in Excel

 

Updating X Axis

  1. Right Click on the X Axis
  2. Click Format Axis

Format X Axis Chart Graph in Excel

 

Format the Axis

  1. Update the Minimum Bound to be the first text value
  2. Update the Maximum Bound to be the last text value

Excel Chart Bounds Min Max in Excel Time Date

 

Update Major Units

  1. Update the Major Units to the value in the Difference Column

Units Time Date Chart in Excel

 

Showing Graph with Date and Time

  1. Change the type to date and time.

Format Graph to show Time Date in Excel

 

2. Click on Size and Properties icon

3. Customize your angle so it is shown on a slant so that it’s easier to see

Angle X Axis Time Date Graph in Excel

 

Final Graph with Date and Time

Final Graph with Date and Time in Excel

 

Create Charts with Dates or Time – Google Sheets

Using the same data as before, we’ll create a similar graph in Google Sheets.

  1. Highlight data
  2. Select Insert
  3. Click on Chart

Create Chart for Date and Time in Google Sheets

 

4. Under Setup on Chart Editor, Select Chart Type

5. Change to Scatter Chart

Create Scatter Chart in Google Sheets Time Date

 

Final Graph with Date and Time

Final Graph for Date and Time in Google Sheets