How to Calculate Daily Averages from Hourly Sensor Data in WPS Office Excel
Question details
The user needs to convert hourly date-time sensor readings into daily averages and separate the date and time components for easier analysis in WPS Office Excel.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user has a worksheet of hourly sensor readings with combined date-time values and wants to summarize them by day.
- Observed behavior
- The goal is to produce a daily average value for each date and separate the date and time into their own columns for analysis.
Make sure the sensor data is stored in a recognized date-time format in WPS Office Excel, and insert blank helper columns for the extracted date, time, and daily average results.
Calculate Daily Averages with a Helper Date Column and AVERAGEIF
Use this formula-based method when you need precise control over the date, time, and daily average columns in WPS Office Excel.
The INT and MOD functions split a combined date-time value into separate date and time components. After creating a date-only helper column, AVERAGEIF can average the sensor reading column for each matching date.
Open your worksheet in WPS Office Excel and confirm that the hourly date-time values are in a column such as A, with sensor readings in column B.
Select columns C and D, right-click, and choose Insert to create blank columns for Time and Date-only values.
In C2 enter =MOD(A2,1) and format the cell as Time. In D2 enter =INT(A2) and format the cell as Date.
In E2 enter =AVERAGEIF($D$2:$D$100,D2,$B$2:$B$100), adjust the ranges to cover your data, and press Enter. Copy the formula down to calculate the daily average for every date.
Group Hourly Data by Day in a PivotTable
Use this method when you want WPS Office Excel to group date-time values automatically and calculate daily averages without helper formulas.
Calculate Sensor Daily Averages in WPS Office Excel
Use WPS Office Excel to split date-time sensor data with INT and MOD, apply AVERAGEIF for daily averages, or group records by day with a PivotTable while keeping full compatibility with Microsoft Excel files.
- 1. Download WPS Office: Get the WPS Office installer from the official WPS site, then install and launch WPS Office Excel.
- 2. Open your hourly sensor workbook: Click File > Open and select the workbook that contains the date-time sensor readings.
- 3. Add helper columns and formulas: Enter =INT(A2) and =MOD(A2,1) to split dates and times, then use =AVERAGEIF($D$2:$D$100,D2,$B$2:$B$100) to calculate daily averages.
- 4. Optionally create a daily PivotTable: Select the data, click Insert > PivotTable, add the date-time field to Rows, set the sensor reading field to Average, and group the rows by Days.
Frequently Asked Questions
How do I extract only the date from a date-time value in WPS Office Excel?
Use =INT(A2) and format the cell as Date. The INT function removes the fractional time portion from the date-time value.
How do I extract only the time from a date-time value?
Use =MOD(A2,1) and format the cell as Time. MOD returns the fractional part of the value, which represents the time.
Which formula calculates daily averages from hourly sensor readings?
Use =AVERAGEIF($D$2:$D$100,D2,$B$2:$B$100), where column D contains date-only values and column B contains sensor readings. Copy the formula down for each date.
Can I calculate daily averages without helper columns?
Yes. Create a PivotTable, drag the date-time field to Rows and the sensor reading field to Values, set the value field to Average, and group the rows by Days.
Why does my AVERAGEIF return a #DIV/0! error?
This usually means no rows match the criteria date. Check that the date-only helper column is formatted consistently and that the criteria cell contains a real date, not text.
