How to Calculate Work Shifts Longer Than 24 Hours in Excel
Question details
The user needs to calculate the total duration of work shifts that span multiple days or exceed 24 hours without encountering formatting errors or negative values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking employee timesheets and calculating total worked hours for overnight or multi-day shifts where standard subtraction results in errors or incorrect durations.
- Observed behavior
- Using standard time subtraction for shifts over 24 hours either produces negative values, hashtag errors (####), or a duration that incorrectly resets to zero after passing the 24-hour mark.
Ensure that your start and end cells contain both the full date and time, as omitting the date makes it impossible for the spreadsheet to calculate shifts lasting longer than 24 hours.
Subtract Complete Date-Times and Apply Elapsed Hours Formatting
This is the standard and most reliable method for calculating multi-day shifts by recording complete date-time values and using a custom format to display hours beyond a 24-hour cycle.
By default, standard time formatting operates on a 24-hour clock. This means a 26-hour shift will display as 2:00. To fix this, you must apply a custom elapsed time format.
In cell A2, type your starting value including the date, for example: 10/25/2023 08:00 AM.
In cell B2, type the ending value including the date, for example: 10/26/2023 10:00 AM.
In cell C2, type the formula =B2-A2 to calculate the difference between the end and start times.
Select cell C2, right-click it, and choose 'Format Cells'. Navigate to the 'Custom' category.
In the Type field, enter [h]:mm and click OK. The brackets instruct the software to display total elapsed hours, showing 26:00 instead of resetting.
Multiply the Duration by 24 for Decimal Hours
This solution is best for payroll and accounting purposes where hours worked must be represented as a standard decimal number instead of a time format.
Easily Calculate Complex Timesheets in WPS Spreadsheet
WPS Spreadsheet is a powerful, free tool that perfectly handles complex time calculations, overnight multi-day shifts, and custom elapsed time formatting seamlessly.
- 1. Open your document: Launch WPS Spreadsheet and open your timesheet file.
- 2. Enter the date-time data: Ensure that your shift data includes both the exact date and the time.
- 3. Input the formula: Type =(End Cell) - (Start Cell) in your Total Hours column.
- 4. Apply elapsed time format: Right-click the result, select Format Cells, choose Custom, and type [h]:mm to accurately display hours over 24.
Frequently Asked Questions
Why does my time calculation show negative numbers or hashtag (####) errors?
This usually occurs when the end time is earlier than the start time in a 24-hour cycle and no date is provided, or if your date format doesn't match your computer's regional settings. Including both the date and time in the cell resolves this.
Can I calculate overnight shifts without typing the date?
Yes, but only if the shift is under 24 hours. You can use the formula =MOD(End-Start, 1) or =IF(End<Start, End+1, End)-Start. For any shift exceeding 24 hours, including the complete dates is mandatory.
How do I stop my total hours from resetting to zero after 24 hours?
You need to apply a custom cell format. By formatting the result cell as [h]:mm instead of h:mm, the square brackets force the spreadsheet to display the total accumulated hours rather than following a standard 24-hour clock cycle.
How can I display time properly as a decimal for payroll?
To represent hours worked as a decimal number (e.g., 8.5 hours instead of 8:30), subtract the start time from the end time and multiply by 24 using the formula =(End-Start)*24. Then format the resulting cell as a standard Number with two decimal places.
