'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Sum Time Values for the Same Date in Excel
Question details
The user needs to sum multiple time durations associated with specific dates without having to manually adjust criteria for each day.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating daily totals of recorded session durations based on matching dates in a timesheet or log.
- Observed behavior
- The user wants to total all time values recorded during a single day dynamically and accurately accumulate the hours.
Ensure your target cell contains a valid numeric Excel date by checking it with the =ISNUMBER(A2) function before applying any sum formulas.
Calculate Daily Time Totals Using the SUMIFS Function
Use the SUMIFS formula to dynamically sum time durations that fall within a 24-hour period of a specified date.
Excel stores dates and times as serial numbers, meaning a single day is represented as an integer of 1. By setting a condition to sum values greater than or equal to the selected date and strictly less than the selected date plus one, you effectively capture all time entries for that specific day without manually changing the criteria.
Click on the cell where you want the total time to appear (for example, DailyTotals!B2).
Type the formula: =SUMIFS(Sessions!$D$2:$D$1500, Sessions!$A$2:$A$1500, ">="&A2, Sessions!$A$2:$A$1500, "<"&A2+1) and press Enter. Adjust the column references based on your actual data layout.
Right-click the result cell and select 'Format Cells' from the context menu.
Navigate to the 'Custom' category and apply the format [h]:mm or [h]:mm:ss to correctly display accumulated time that exceeds 24 hours.
Easily Sum Data by Date with WPS Spreadsheet
WPS Office Spreadsheet fully supports complex formulas like SUMIFS and custom time formatting. It provides a familiar, fast, and highly compatible environment for managing your daily logs and calculating durations seamlessly.
- 1. Open your data file: Launch WPS Spreadsheet and open your timesheet or session log file containing the dates and durations.
- 2. Input the calculation formula: Select your target cell and input the SUMIFS formula to calculate total durations based on your selected date cell.
- 3. Format the total cell: Press Ctrl+1 to open the Format Cells dialog, select Custom, and enter [h]:mm:ss to display the total accumulated hours correctly.
Frequently Asked Questions
Why does my sum show a lower time than expected when totaling durations?
Standard time formatting resets after 24 hours (for example, 25 hours displays as 1:00). To fix this, right-click the cell, select Format Cells, choose Custom, and use brackets around the hour like [h]:mm or [h]:mm:ss to display cumulative total hours.
How can I check if my date cells are actually recognized as dates?
You can use the formula =ISNUMBER(A2). If the result is TRUE, the software correctly recognizes the cell as a valid numeric date. If it returns FALSE, the date is likely stored as text and needs to be converted.
Can I use SUMIF instead of SUMIFS for this calculation?
Because calculating time within a specific date requires two criteria (greater than or equal to the start of the day, AND less than the start of the next day), you must use SUMIFS. The basic SUMIF function only supports a single condition.
