Dates and Times On X Axis of Line Chart

[deanfran]

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.

 

[GraH]

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.

 

[deanfran]

I'm afraid that results in this. The columns look like this.

 

[GraH]

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]

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.

 

[Jon Peltier]

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.

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

 

[Jon Peltier]

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]

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]

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.