Calculation Issues

How to Calculate Average Measurements by Hour and Date in Excel

Aamir Naveed Akram Oct 22, 2026 868 views

Question details

Calculate average measurement values grouped by specific hours of specific days across a multi-year dataset.

How to Calculate Average Measurements by Hour and Date in Excel

Product: Excel
Device & OS: not provided
Scenario: Analyzing a long-term data set containing timestamps and measurement values to find the average value for each hour of every individual day.
Observed behavior: The user needs a method to ensure identical hours from different dates (e.g., 9:00 AM on Jan 1st and 9:00 AM on Jan 2nd) are calculated separately rather than being incorrectly grouped together.

Before you start

Ensure your timestamp column contains valid Excel date/time values, not plain text, so that formulas and PivotTables can correctly extract the date and hour information.

Solution 1Recommended

Use a Helper Column and a PivotTable

This is the most efficient method for large datasets spanning multiple years, as PivotTables quickly aggregate data without complex formulas.

To prevent Excel from grouping the same hours from different days or years together, you need to create a unique identifier for each specific hour of each specific day using a helper column.

Create a Date and Hour helper column

Insert a new column next to your timestamps. Assuming your timestamp is in A2, enter the formula =INT(A2)+TIME(HOUR(A2),0,0) and drag it down. This strips the minutes and seconds, leaving only the specific date and hour.

Format the helper column

Select the new helper column, right-click, select 'Format Cells', and apply a Custom format like 'mm/dd/yyyy hh:mm' to easily read the grouped hours.

Insert a PivotTable

Highlight your entire data range (including the new helper column and the measurements). Go to the 'Insert' tab on the ribbon and click 'PivotTable'.

Configure the PivotTable fields

Drag your new helper column into the 'Rows' area. Then, drag your measurement values column into the 'Values' area.

Change the aggregation to Average

Click the drop-down arrow on the measurement in the 'Values' area, select 'Value Field Settings', choose 'Average' from the list, and click 'OK'.

Performance Tip: PivotTables process thousands of rows much faster than array formulas, making this the best approach for three-year datasets.

Solution 2

Use a SUMPRODUCT Formula

Ideal if you want to extract the average for a specific date and hour directly into a dashboard or report without generating a PivotTable.

Data Analysis Made Easy

Calculate Hourly Averages Effortlessly in WPS Spreadsheet

WPS Spreadsheet provides powerful functions and lightning-fast PivotTable capabilities to analyze multi-year datasets seamlessly. Calculate average measurements by date and hour exactly as you would in Microsoft Excel.

1. Prepare Data: Open your dataset in WPS Spreadsheet and add a helper column to extract the date and hour using =INT(A2)+TIME(HOUR(A2),0,0).
2. Create PivotTable: Go to the Insert tab and click PivotTable to summarize your multi-year data instantly.
3. Set to Average: Drag the helper column to Rows, measurements to Values, and change the Value Field Settings to Average.

100% compatibility with Microsoft Excel formulas and PivotTablesProcess large datasets spanning multiple years without laggingLightweight application with a familiar, easy-to-use interfaceFree alternative with robust professional data analysis tools

Frequently Asked Questions

How do I extract just the date from a timestamp in Excel?

You can use the INT function (e.g., =INT(A2)) to remove the time portion of a timestamp, leaving only the whole number that represents the date. Ensure you format the result cell as a 'Short Date'.

Why is my SUMPRODUCT formula returning a #DIV/0! error?

This error occurs when the second part of your SUMPRODUCT formula (the count) results in zero because there are no matching records for the specified date and hour. You can wrap your entire formula in an IFERROR function, like =IFERROR(your_formula, 0), to display a zero or blank instead.

Can I use AVERAGEIFS instead of SUMPRODUCT for date and time?

Yes, AVERAGEIFS is often faster for large datasets. You can use it by setting two criteria for the timestamp column: one checking if it is greater than or equal to your target hour, and another checking if it is strictly less than the next hour.

Excel & Data Problems