Forum Discussion
Deleting and or Consolidating Date info for Pivot Tables
Folks,
I am a little new to excel's data processing features. I've done all the basic stuff for years, but I am diving in deeper. I am having trouble with correlating data streams from multiple sources that have specific time stamps. I need to utilize the date and hour times with the associated data, but I can't figure out how to correlate this between multiple data streams. I am having trouble trying to associate the time values between different data streams. I am sure there is a way to do this. Each data stream has a unique time stamp with DATE HH:MM:SS. I need to match up the dates and hours on the different streams. I want to plot multiple data streams on one graph or interactive pivot table. I can't figure out how to get excel to ignore minutes and seconds between readings and incorporate it together. I really just want one hourly reading instead of the 6 reading every ten-ish minutes.
For Example. I have this record. My other records are a few seconds or minutes off. Some data missing for hours or days. I cannot figure out how to correlate this sort of data together. Any advice would be appreciated.
| Time | Level Sensor 1 [ft] | Temperature Sensor 2 [°F] |
| 2024-05-08T09:05:58.800-07:00 | ||
| 2024-05-08T13:09:23.000-07:00 | 0 | 32 |
| 2024-05-08T13:24:03.000-07:00 | 12.5 | 32.9 |
| 2024-05-08T13:34:02.000-07:00 | 11.8 | |
| 2024-05-08T13:44:02.000-07:00 | 11.2 | |
| 2024-05-08T13:54:02.000-07:00 | 10.5 | |
| 2024-05-08T14:04:01.000-07:00 | 10.5 | |
| 2024-05-08T14:14:01.000-07:00 | 10.2 | |
| 2024-05-08T14:24:01.000-07:00 | 10.2 | |
| 2024-05-08T14:34:01.000-07:00 | 9.8 | |
| 2024-05-08T14:35:16.000-07:00 | 9.8 | |
| 2024-05-08T14:45:57.000-07:00 | 9.8 | |
| 2024-05-08T14:55:56.000-07:00 | 9.8 | |
| 2024-05-08T15:05:56.000-07:00 | 9.5 | |
| 2024-05-08T15:15:57.000-07:00 | 9.5 | |
| 2024-05-08T15:25:56.000-07:00 | 9.5 | |
| 2024-05-08T15:35:56.000-07:00 | 9.5 | |
| 2024-05-08T15:45:56.000-07:00 | 9.5 | |
| 2024-05-08T15:55:55.000-07:00 | 9.5 | |
| 2024-05-08T16:05:55.000-07:00 | 9.2 | |
| 2024-05-08T16:15:55.000-07:00 | 9.5 | |
| 2024-05-08T16:25:54.000-07:00 | 9.2 | |
| 2024-05-08T16:35:54.000-07:00 | 17.1 | |
| 2024-05-08T16:45:53.000-07:00 | 8.9 | |
| 2024-05-08T16:55:53.000-07:00 | 9.2 | |
| 2024-05-08T17:05:54.000-07:00 | 9.2 | |
| 2024-05-08T17:15:53.000-07:00 | 9.5 | |
| 2024-05-08T17:25:53.000-07:00 | 9.5 | |
| 2024-05-08T17:35:53.000-07:00 | 9.5 | |
| 2024-05-08T17:45:52.000-07:00 | 9.5 | |
| 2024-05-08T17:55:52.000-07:00 | 9.5 | |
| 2024-05-08T18:05:52.000-07:00 | 9.5 | |
| 2024-05-08T18:15:51.000-07:00 | 9.5 | |
| 2024-05-08T18:25:51.000-07:00 | 9.5 | |
| 2024-05-08T18:35:51.000-07:00 | 9.5 | |
| 2024-05-08T18:45:50.000-07:00 | 9.5 | |
| 2024-05-08T18:55:50.000-07:00 | 9.5 | |
| 2024-05-08T19:05:51.000-07:00 | 9.5 | |
| 2024-05-08T19:15:50.000-07:00 | 9.5 | |
| 2024-05-08T19:25:50.000-07:00 | 9.5 | |
| 2024-05-08T19:35:51.000-07:00 | 9.2 | |
| 2024-05-08T19:45:49.000-07:00 | 9.5 | |
| 2024-05-08T19:56:50.000-07:00 | 9.2 | |
| 2024-05-08T20:06:49.000-07:00 | 9.2 | |
| 2024-05-08T20:16:48.000-07:00 | 9.2 | |
| 2024-05-08T20:26:48.000-07:00 | 9.2 | |
| 2024-05-08T20:36:48.000-07:00 | 9.2 | |
| 2024-05-08T20:46:48.000-07:00 | 9.2 | |
| 2024-05-08T20:56:47.000-07:00 | 9.2 | |
| 2024-05-08T21:06:49.000-07:00 | 9.2 | |
| 2024-05-08T21:16:50.000-07:00 | 9.2 | |
| 2024-05-08T21:26:47.000-07:00 | 9.2 | |
| 2024-05-08T21:36:47.000-07:00 | 9.2 | |
| 2024-05-08T21:46:47.000-07:00 | 9.2 | |
| 2024-05-08T21:56:46.000-07:00 | 9.2 | |
| 2024-05-08T22:06:46.000-07:00 | 9.2 | |
| 2024-05-08T22:16:46.000-07:00 | 8.9 | |
| 2024-05-08T22:26:45.000-07:00 | 8.9 | |
| 2024-05-08T22:36:45.000-07:00 | 9.2 | |
| 2024-05-08T22:46:45.000-07:00 | 8.9 | |
| 2024-05-08T22:56:44.000-07:00 | 9.2 | |
| 2024-05-08T23:06:45.000-07:00 | 8.9 | |
| 2024-05-08T23:16:45.000-07:00 | 8.9 | |
| 2024-05-08T23:26:45.000-07:00 | 8.9 | |
| 2024-05-08T23:36:44.000-07:00 | 8.9 | |
| 2024-05-08T23:46:44.000-07:00 | 8.9 | |
| 2024-05-08T23:56:44.000-07:00 | 8.9 | |
| 2024-05-09T00:06:43.000-07:00 | 8.9 | |
| 2024-05-09T00:16:43.000-07:00 | 8.9 | |
| 2024-05-09T00:26:43.000-07:00 | 8.9 | |
| 2024-05-09T00:36:42.000-07:00 | 8.9 | |
| 2024-05-09T00:46:42.000-07:00 | 8.9 | |
| 2024-05-09T00:56:42.000-07:00 | 8.9 | |
| 2024-05-09T01:06:42.000-07:00 | 8.9 | |
| 2024-05-09T01:16:42.000-07:00 | 8.9 | |
| 2024-05-09T01:26:42.000-07:00 | 8.9 | |
| 2024-05-09T01:36:40.000-07:00 | 8.9 | |
| 2024-05-09T01:46:39.000-07:00 | 8.9 | |
| 2024-05-09T01:56:39.000-07:00 | 8.5 | |
| 2024-05-09T02:06:41.000-07:00 | 8.5 |
I am struggling with getting this in a format that I can associate with other similar data.
Please let me know if you have any tips.
Thank You,
Ben
1 Reply
You can use the following function to extract the date and hour from the timestamp (in cell A3). If you insert this in an auxiliary column, you can use this in the pivot table.
Date: =INT(VALUE(TEXTBEFORE(SUBSTITUTE(A3,"T"," "),"."))) Hour: =HOUR(VALUE(TEXTBEFORE(SUBSTITUTE(A3,"T"," "),".")))
