Dates and Times On X Axis of Line Chart

deanfran

Board Regular
Joined
Sep 9, 2011
Messages
52
I've searched Excel and the internet and haven't found a good way to do this. I have tons of data that has two columns Date and Time. The data points are on 5 minute intervals, so in a given day, there are 288 rows for each day and obviously each row has the same date value. I graph this data monthly and quarterly. At present I just use the Time Column on the X Axis for cleanliness sake, but I would love to find a way to have all of the times, and just each date once. I don't know if there's a way to do this, but I figured if there was, this would be the place to come for the answer.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi deanfran,

Would it be something close to this?
Using Multi-Level Category Labels, making sure the date in the data is repeated only once.
1590674932017.png
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I don't know what the visual should be like, but on the site of the Frankens' team there is a tutorial on scrolling through a large dataset while keeping a smaller visual of the whole thing.
As for the Axis settings, I still see you repeat the date in the dataset. I did not repeat the date and the Axis was less cluttered.
 

deanfran

Board Regular
Joined
Sep 9, 2011
Messages
52

ADVERTISEMENT

I don't know what the visual should be like, but on the site of the Frankens' team there is a tutorial on scrolling through a large dataset while keeping a smaller visual of the whole thing.
As for the Axis settings, I still see you repeat the date in the dataset. I did not repeat the date and the Axis was less cluttered.
The data comes from a datalogging device, and yes the date is repeated for every row. Actually each data point (row) has a text string containing the date and time that gets parsed out in Excel via the text to columns tool. It originally looks like this. 00:02:13 05-01-2020. Once it gets pasted into Excel, Excel without any formatting changes by me, turns it into this 5/1/2020 12:02:13 AM, I then use text to columns and some formatting to get this.
DT.png
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Probably you have three columns, eh? Date and Time and some value you're tracking.

So make sure you only put the date in the date column for the first data value for that date. Use the first two columns (date and time) for your X values, and the third column as Y values. The data below extends down to row 122.

Date-Time Dual Tiered Axis.png


Edit: I see after posting this (it sat on my backup computer overnight) that @GraH answered with much the same answer.
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

I'm afraid that results in this. The columns look like this. View attachment 14966View attachment 14965

Note that GraH had his data values only in the first row for each date. You have repeated the dates in every row. That will help somewhat, though with so many times per day, it will not be very legible.
 

deanfran

Board Regular
Joined
Sep 9, 2011
Messages
52
So it appears this boils down to manually clearing the date value from all rows but the first one for each day's data set in order to get what I'm looking for.
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,943
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
So it appears this boils down to manually clearing the date value from all rows but the first one for each day's data set in order to get what I'm looking for.
Sure, or it might be easier to use a pivot table.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,588
Messages
5,597,049
Members
414,117
Latest member
marblepoint

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top