When summing employee timesheets or calculating weekly overtime in Excel, two frustrating issues frequently occur: your total hours roll over and reset after reaching 24 hours (for example, showing 3:00 instead of 27:00), or the calculation halts completely with a #VALUE! error. A #VALUE! error indicates that Excel is trying to perform arithmetic on cells that contain text strings instead of numeric time values.
The clock reset is a formatting problem solved by applying the [h]:mm custom number format, while the #VALUE! error indicates that time entries were entered or imported as text.
How to Sum Hours Over 24 and Fix #VALUE! Errors in Excel
To display cumulative time past 24 hours, select your summary cell, press Ctrl + 1, choose Custom, and set the format to
[h]:mm. If your formula returns a#VALUE!error, your time entries are stored as text; clean them by converting the text to valid time serials using theTIMEVALUEfunction or using Text to Columns.
Why Excel Resets Time or Throws #VALUE!
To troubleshoot time calculations, you must understand how Excel handles time internally:
- Time is a fractional day: In Excel, one whole day equals
1.0. Twelve hours is0.5, six hours is0.25, and one hour is1/24(approximately0.041667). - Standard clock display (
hh:mm): Formats likehh:mmorh:mm AM/PMrepresent the time on a wall clock. When cumulative hours reach 24 (or1.0), the display rolls over to00:00and displays only the remaining balance. The underlying decimal still holds the true sum, but the cell hides the days. - Text entries trigger
#VALUE!: Standard arithmetic operators (+, , ) require numbers. If an employee enters8:00 hrs, includes accidental spaces, or exports data from payroll software that wraps timestamps in text strings, Excel cannot perform time math and throws#VALUE!.
If your timesheet imports spaces alongside times, see #VALUE! in Arithmetic: Mathematical operations on Cells with Spaces for targeted cleanup rules.
Step 1: Fix the 24-Hour Reset Using [h]:mm
If your formula =SUM(B2:B8) calculates without errors but returns an impossibly small number (such as 6:30 when the employee worked 30.5 hours), the issue is purely visual.
Standard Format (hh:mm): 24 hours + 6.5 hours = 06:30
Elapsed Format ([h]:mm): 24 hours + 6.5 hours = 30:30
- Select the cell or range displaying the total hours.
- Press Ctrl + 1 (or Cmd + 1 on Mac) to open the Format Cells dialog.
- Under the Number tab, click Custom at the bottom of the Category list.
- In the Type input box, type:
[h]:mm - Click OK.
The square brackets around [h] instruct Excel to display elapsed hours as a cumulative sum rather than cycling every 24 hours. If you need to track seconds as well, use [h]:mm:ss.
Step 2: Diagnose and Fix the #VALUE! Error
If the cell displays #VALUE!, one or more cells in the calculation chain contain non-numeric data.
Identifying the Problem Cell
In an adjacent column, test your time entries with the ISNUMBER function:
=ISNUMBER(B2)
Drag this formula down your entries. Any cell that returns FALSE is text and is breaking your summary formulas.
Method A: Fast In-Place Repair via Text to Columns
If your timesheet export contains numbers formatted as text:
- Select the column of imported times.
- Go to the Data tab on the Ribbon and click Text to Columns.
- Select Delimited and click Next.
- Uncheck all delimiter boxes and click Next.
- Under Column data format, leave it as General and click Finish.
This forces Excel to re-evaluate the contents of each cell, converting text timestamps (like "08:30") into native decimal time serials.
Method B: Formula Conversion with TIMEVALUE
If your sheet imports mixed strings or formulas that cannot be modified with Text to Columns, wrap the reference in TIMEVALUE or add 0:
=TIMEVALUE(B2)
Alternatively, coerce text into numbers using standard arithmetic:
=IF(ISNUMBER(B2), B2, TIMEVALUE(TRIM(B2)))
If your attendance tracker mixes actual times with labels like “Sick” or “PTO”, formulas like SUM will normally ignore text, but direct addition (=B2+B3+B4) will immediately crash with #VALUE!. See Attendance Trackers: Fixing #VALUE! when “Vacation” text is entered in a “Days” column. to handle non-numeric status codes safely.
Step 3: Converting Total Hours to Regular Decimal Pay
A common payroll mistake occurs when multiplying elapsed time by an hourly wage:
Total Hours (formatted as [h]:mm) = 40:00
Hourly Rate = $25.00
Incorrect Formula: = 40:00 * 25.00 -> Output: $41.67
Because Excel treats 40 hours as 1.6667 days (40 / 24), multiplying directly by 25 computes pay for 1.67 days, not 40 hours.
The Conversion Formula
To calculate gross pay, you must multiply the time serial by 24 to convert fractional days into decimal hours:
Gross Pay = Total_Time_Cell * 24 * Hourly_Rate
Correct Calculation: 1.6667 * 24 = 40.0 decimal hours
40.0 * $25.00 = $1,000.00
Format the final gross pay cell as Currency or Accounting, not [h]:mm.
Step 4: Handling Night Shifts and Midnight Crossovers
Calculating hours worked by subtracting start time from end time (End_Time - Start_Time) causes a negative time result whenever a shift crosses midnight. In Excel’s standard 1900 date system, negative times cannot be displayed and show as a row of hash marks (#######) or trigger calculation errors in downstream logic.
Start Time: 10:00 PM (22:00)
End Time: 06:00 AM (06:00)
06:00 - 22:00 = -16:00 (Error)
The MOD Function Solution
To automatically handle overnight shifts without complex IF statements, use the MOD function:
=MOD(End_Time - Start_Time, 1)
The MOD function with a divisor of 1 returns the positive fractional remainder. If the difference is negative, MOD automatically adds 1 (one full 24-hour cycle), yielding the correct duration of 8:00.
When payroll involves different pay multipliers for second and third shifts, combining midnight crossovers with premium pay rates can cause nested calculation breaks. For step-by-step methods to separate overnight hours from standard schedules, refer to Shift Differential: Fixing #VALUE! errors in night-shift vs. day-shift rate multipliers.
If timesheets span multiple days or include interview tracking across multiple office locations, ensure your date serials account for standard timezones using Timezone Conversion: Handling #VALUE! when calculating interview times across UTC offsets. When projecting monthly hours against scheduled work weeks, combine your hour calculations with Workday Calculation: Fixing NETWORKDAYS when it fails to account for regional bank holidays..