summarizing sensor Hourly Data to daily data

[Khalil_Ammar]

I have for each daily date hourly data in the same cell, i.e., the cell contains the date and hour. I am interested in calculating the average daily value from the hourly soil moisture sensor values. How to do it in Excel. Also, if it is possible to split the cell of date and hour into two columns, date and hours?

Thank you

[#2]

@Khalil_Ammar 

=AVERAGE(IF($D$3:$D$18=D3,$B$3:$B$18))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

[#3]

@Khalil_Ammar 

Or use AVERAGEIF.

=AVERAGEIF($D$3:$D$18,D3,$B$3:$B$18)

See attached example, based on that by @OliverScheurich 

For fun, I used INT instead of ROUNDDOWN and MOD instead of subtracting the date.

[#4]

@Khalil_Ammar 
Another option for averaging is PivotTable with grouping datetime as desired

[#5]

Thank you very much@Hans Vogelaar. It worked. 

[#6]

Thank you very much, Sergie. This was very helpful.