summarizing sensor Hourly Data to daily data

Copper Contributor

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

5 Replies



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







See attached example, based on that by @OliverScheurich 

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

Another option for averaging is PivotTable with grouping datetime as desired


Thank you very much@Hans Vogelaar. It worked. 

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