find highest values (speeds) by day and hour

[breilly00]

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

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​

 

[bob33]

Easier if you share an example file.

 

[breilly00]

Easier if you share an example file.

Bob,

I know that there is a way to share the file; but, it evades me. How could I get the file to you???

 

[Saba Sabaratnam]

Based on the limited data that you provided and assuming that you are using Excel 365, you could use SORTBY function as shown below.

Enter the following formula in I2 to sort speed by date

=SORTBY($A$1:$B$7,B1:B7,-1)

Enter the following formula in L2 to sort speed by hour.

=SORTBY(E1:F7,F1:F7,-1)

Kind regards

Saba

=

 

[breilly00]

gently dropping 0's and 1's that translate to WONDERFUL to the southern neighbor. TY SABA --- stay safe and have no worries.