Extracting data from an electronic speed control sign.
the Raw data extracted can be narrowed down to date/time and speed columns. The times will be unique to the HH:MM:SS. Narrowed down data sample below
Each month the data will be extracted from the sign and loaded into Excel. We are trying to find out on what days the highest speeding occurs and if most speeding occurs at a certain. hour. I can extract the hour and day from the time column. But, cannot find a sample excel graph to use (if that is even possible). Do I need formuls, pivot tables, etc. to be able to answer the following to questions.
In a given month
what days (1-30) of the month recorded the highest speeds - sorted by high low
what hour (1-24) of the month recorded the highest speeds - sorted high to low
knowing this we can put traffic patrol on the roads during those days/times
sample of what I can get my excel sheet to look like.
the Raw data extracted can be narrowed down to date/time and speed columns. The times will be unique to the HH:MM:SS. Narrowed down data sample below
date | speed |
4/1/2021 9:48 | 66 |
4/1/2021 11:13 | 66 |
4/1/2021 15:29 | 66 |
Each month the data will be extracted from the sign and loaded into Excel. We are trying to find out on what days the highest speeding occurs and if most speeding occurs at a certain. hour. I can extract the hour and day from the time column. But, cannot find a sample excel graph to use (if that is even possible). Do I need formuls, pivot tables, etc. to be able to answer the following to questions.
In a given month
what days (1-30) of the month recorded the highest speeds - sorted by high low
what hour (1-24) of the month recorded the highest speeds - sorted high to low
knowing this we can put traffic patrol on the roads during those days/times
sample of what I can get my excel sheet to look like.
date | speed | Day | Hour | Speed | |
4/1/2021 9:48 | 66 | 1 | 9 | 66 | |
4/1/2021 11:13 | 66 | 1 | 11 | 66 | |
4/1/2021 15:29 | 66 | 1 | 15 | 66 | |
4/1/2021 17:03 | 66 | 1 | 17 | 66 | |
4/2/2021 9:14 | 62 | 2 | 9 | 62 | |
4/2/2021 10:06 | 66 | 2 | 10 | 66 |