Calculation Issues

How to Sum Values Between Specific Times in Excel

Maira Mehtab Sep 21, 2026 868 views

Question details

The user wants to calculate the sum of readings recorded between 8:00 AM and 11:00 PM when the source column contains both dates and times in the same cell.

Product: Excel
Device & OS: not provided
Scenario: Calculating conditional sums based on time intervals extracted from combined date-time cells.
Observed behavior: Need to isolate the time portion from a combined date-time cell to evaluate it as criteria for summing associated numeric values.

Before you start

Ensure that your date and time values are recognized by Excel as valid date-time serial numbers rather than text strings, as calculation formulas require numerical values to function properly.

Solution 1Recommended

Use the LET and SUM Functions to Filter by Time

Extract the time from the combined date-time cells and use an array formula to sum the corresponding readings within the specified time range.

When dates and times are combined in a single cell, standard SUMIFS cannot directly evaluate the time portion. By using the LET function combined with TIME, HOUR, and MINUTE, you can extract just the time value for accurate comparison.

Identify your data ranges

Assume your combined date and time values are located in the range B2:B15, and the readings you want to sum are located in A2:A15.

Enter the array formula

Select an empty cell where you want the total to appear and enter the formula: =LET(dt,B2:B15,t,TIME(HOUR(dt),MINUTE(dt),0),SUM((t>=TIME(8,0,0))*(t<=TIME(23,0,0))*A2:A15))

Execute the calculation

Press Enter to calculate the sum. The formula evaluates the time portion of each cell, checks if it falls between 8:00 AM (8,0,0) and 11:00 PM (23,0,0), and sums the corresponding values in column A.

Version Compatibility: The LET function is available in newer versions of Excel (Microsoft 365, Excel 2021). If you are using an older version, you will need to use a SUMPRODUCT formula instead.

Solution 2

Use SUMPRODUCT with the MOD Function for Older Excel Versions

For versions of Excel that do not support the LET function, use SUMPRODUCT coupled with the MOD function to easily extract the time portion of a date-time cell.

Advanced Calculations in WPS Spreadsheet

Perform Complex Time-based Calculations Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, including SUMPRODUCT and time functions, allowing you to seamlessly calculate values based on specific time intervals extracted from combined date-time cells.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the date, time, and reading values.
2. Select an output cell: Click on the cell where you want the total conditional sum to appear.
3. Input the calculation formula: Type =SUMPRODUCT((MOD(B2:B15,1)>=TIME(8,0,0))*(MOD(B2:B15,1)<=TIME(23,0,0)),A2:A15) and press Enter to instantly calculate your time-filtered total.

100% compatible with Microsoft Excel formulas like SUMPRODUCT, TIME, and MOD.Lightweight and fast, handling complex arrays and large datasets effortlessly.Familiar interface makes switching from Excel completely frictionless.Completely free alternative to expensive office software.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I just use the SUMIFS function with combined date-time cells?

The SUMIFS function looks at the literal combined cell value. Since a combined date-time cell includes the date (a large integer), trying to match just the time (a decimal fraction) using standard comparison operators in SUMIFS will not work. You must extract the time first using functions like MOD, HOUR, or TIME.

How does Excel store dates and times internally?

Excel stores dates as sequential serial numbers (e.g., January 1, 1900, is 1) and times as fractional values of a 24-hour day (e.g., 12:00 PM is 0.5). A combined date-time is a number with both an integer portion (the date) and a decimal portion (the time).

What should I do if the formula returns a #VALUE! error?

Ensure that your target ranges (e.g., B2:B15 and A2:A15) are exactly the same size. Also, verify that the date-time cells are formatted as numbers or dates, not as text. You can use the VALUE function to convert text strings to serial numbers if necessary.

Excel & Data Problems