Function Problems

Calculate Daily Averages with Times Using AVERAGEIFS in Excel

Huda Qurayshi Oct 8, 2026 873 views

Question details

Calculate daily averages from a dataset where the source dates include specific time values, summarizing metrics for individual calendar days.

How to Calculate Daily Averages for Dates with Times Using AVERAGEIFS

Product: Excel
Device & OS: not provided
Scenario: A user has source data categorized by dates with various times and needs to aggregate averages for multiple columns (e.g., Yellow, Green, Blue values) by matching each summary date with the date portion of the source data.
Observed behavior: Standard exact-match lookups fail to aggregate the data because the time components in the source dates prevent a direct match with plain calendar summary dates.

Before you start

Ensure your source dates are formatted as valid date/time numbers, and identify the exact cell ranges for both your criteria (dates) and your target values before writing the formula.

Solution 1Recommended

Use AVERAGEIFS with Date Boundaries (For Dates Including Times)

When your date column includes time values, you must use boundary conditions to capture all times within that specific 24-hour period.

Because Excel stores dates and times as serial numbers, a date with a time is technically larger than the plain date. To match a full day's worth of data, we set the criteria to be greater than or equal to the target date, and strictly less than the following day.

Select the summary cell

Click on the destination cell where you want the first daily average to appear (e.g., B13).

Enter the AVERAGEIFS formula

Type the formula using boundary logic: =AVERAGEIFS(C$2:C$9, $A$2:$A$9, ">="&$A13, $A$2:$A$9, "<"&($A13+1))

Adjust references

Ensure your average range (C$2:C$9) has a relative column but absolute rows so it adjusts when dragged across, while your criteria range ($A$2:$A$9) remains fully absolute.

Apply formula to other columns

Press Enter, then drag the fill handle horizontally to calculate averages for the remaining value columns (e.g., Green and Blue values).

Use AVERAGEIFS with Date Boundaries (For Dates Including Times)

Formula Logic Explained: The boundary '<'&(A13+1) automatically calculates the next day's date. Using the strictly less-than operator '<' ensures that records up to 11:59:59 PM are included, without accidentally capturing midnight of the next day.

Solution 2

Use Standard AVERAGEIFS (For Exact Dates Without Times)

If your source data contains plain dates with no time values attached, a simpler exact-match AVERAGEIFS formula is perfectly sufficient.

WPS Spreadsheet Solution

Calculate Complex Averages Seamlessly in WPS Office

WPS Spreadsheet offers powerful built-in support for advanced statistical functions like AVERAGEIFS. You can easily manage dates, times, and complex data summaries within its intuitive, high-performance interface.

1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the dates and values.
2. Format your dates: Select your source date column, right-click, and choose 'Format Cells' to ensure they are recognized as Date/Time values.
3. Input the AVERAGEIFS formula: Select your summary cell and enter the boundary formula: =AVERAGEIFS(C$2:C$9, $A$2:$A$9, ">="&$A13, $A$2:$A$9, "<"&($A13+1)).
4. Drag to fill: Hit Enter and use the fill handle to copy the formula across all required metric columns instantly.

Fully compatible with Microsoft Excel formulas including AVERAGEIFS and SUMIFS.Lightweight software that processes large datasets quickly without lag.Built-in advanced date and time formatting tools.Free to use for everyday data analysis and reporting.

microsoft office alternative - wps office

Frequently Asked Questions

Why does AVERAGEIFS return a #DIV/0! error when averaging dates with times?

This error usually occurs if the criteria find no matching records. Because an exact match like =A13 looks for midnight exactly (00:00:00), any source dates with a time later in the day will be ignored. You must use boundary criteria like >=

Can I remove the time from my date cells instead of using a complex formula?

Yes. You can extract just the calendar date by using the INT() function in a new helper column (e.g., =INT(A2)). Once you have a column of plain dates, you can use a basic exact-match AVERAGEIFS formula on that new column.

How do I average multiple columns at once?

AVERAGEIFS only averages one specific range at a time. To average multiple columns, you need to write the formula for the first column and use mixed references (like C$2:C$9) so that when you drag the formula horizontally, the column letters change while the rows stay locked.

Does AVERAGEIFS ignore empty cells in the average calculation?

Yes, AVERAGEIFS automatically ignores blank cells or cells containing text within the average range. It only factors numeric values into the denominator, ensuring your daily averages remain accurate.

Excel & Data Problems