How to Add Time Periods and Display Weekly Totals Over 24 Hours in Excel
Question details
The user needs to sum daily time values to calculate weekly totals and display cumulative hours correctly when the total exceeds the standard 24-hour limit.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating cumulative weekly worked hours or total elapsed time based on a list of daily time entries.
- Observed behavior
- Standard time formatting resets the hour count to zero after reaching 24 hours, failing to show the correct cumulative weekly total.
Ensure that all the daily time values you are trying to sum are recognized as numeric time entries in Excel, rather than text strings.
Sum Daily Times and Apply a Custom Cumulative Format
Use the SUM function combined with a bracketed custom time format to prevent Excel from resetting the hour count after 24.
By default, Excel interprets time as a time of day, meaning anything past 23:59 rolls over to a new day. Placing brackets around the hour in your custom format forces Excel to display elapsed or cumulative time.
Select the cell where you want the weekly total to appear and enter the formula =SUM(H296:H304), replacing the range with your actual daily time totals.
Right-click the cell containing your sum and select "Format Cells" from the context menu.
Go to the "Number" tab and select "Custom" from the list of categories on the left.
In the "Type" input box, enter [h]:mm or [h]:mm;@. The square brackets instruct Excel to display hours cumulatively.
Click "OK" to apply the formatting. The cell will now properly display totals that exceed 24 hours.
Verify and Convert Text-Formatted Time Entries
If your SUM formula returns an incorrect result or exactly zero, your time entries might be stored as text rather than numeric values.
Easily Calculate Weekly Time Totals with WPS Office
WPS Spreadsheet provides robust formula calculation and complete support for custom number formatting. You can seamlessly sum time entries and track cumulative hours over 24 hours without compatibility issues.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open the timesheet workbook.
- 2. Sum your time entries: Select your total cell and type your SUM formula, for example =SUM(B2:B8).
- 3. Format the total cell: Right-click the total cell, select "Format Cells", and navigate to "Custom" under the Number tab.
- 4. Apply cumulative brackets: Type [h]:mm into the Type field and click "OK" to display your total accumulated hours.
Frequently Asked Questions
Why does my Excel time sum reset to zero after 24 hours?
By default, Excel applies a "Time of Day" format (like a clock). Once the total hits 24 hours, it rolls over to a new day and starts the clock from 0:00. You must use the custom format [h]:mm to tell Excel to show cumulative, elapsed hours instead of a clock time.
Can I format cumulative time totals to show seconds as well?
Yes, you can easily include seconds in your total by modifying the custom format. Enter [h]:mm:ss in the Custom Format Type box to display hours, minutes, and seconds cumulatively.
How do I multiply my total time hours by an hourly wage?
Because Excel stores time as a fraction of a day (where 24 hours = 1), you cannot multiply the time cell directly by the wage. First, multiply the total time cell by 24 to convert it into a standard decimal number, then multiply that result by your hourly rate. For example: =(A1*24)*Hourly_Wage.
What if I need to subtract a lunch break from the daily total?
You can subtract break times using standard math formulas as long as all entries are formatted as time. Use a formula like =End_Time - Start_Time - Break_Time to get the net daily hours before summing them for the week.
