How to Calculate Excel Time Differences Over 24 Hours or Across Midnight
Question details
Calculate the exact duration between two times in Excel, especially when the work period or transit time crosses into the next day or spans multiple days.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating shifts, project hours, or transit periods where the end time occurs on a different day than the start time.
- Observed behavior
- Simple time subtraction yields incorrect hours or negative value errors (####) when periods cross midnight, and durations over 24 hours reset incorrectly if standard time formats are applied.
Verify that your start and end time cells contain both the date and the time (e.g., mm/dd/yyyy hh:mm AM/PM) so the calculation correctly recognizes the transition into the next day.
Calculate Duration in Hours and Minutes
Use standard cell subtraction combined with a custom cell format to prevent hours from resetting after 24 hours.
Excel stores dates and times as serial numbers. When subtracting a start time from an end time, the result is a decimal representing the fraction of a day. Using the standard time format will reset the display at 24 hours. You must use a custom format enclosed in brackets to display total accumulated hours.
Select the cell where you want the result to appear and type the formula =End_Time - Start_Time (for example, =B1-A1).
Press Enter to calculate. Then, right-click the result cell and select 'Format Cells' from the context menu.
In the Format Cells dialog, go to the 'Number' tab and select 'Custom' from the category list. In the Type box, enter [h]:mm and click OK.
Calculate Duration in Decimal Hours
Convert the serial time difference into standard decimal hours by multiplying the result by 24.
Calculate Complex Time Differences Seamlessly in WPS Spreadsheet
WPS Spreadsheet handles date-time serial values and custom formatting exactly like Microsoft Excel, allowing you to calculate shifts, transit times, and multi-day durations flawlessly with the same formulas.
- 1. Enter your data: Open your document in WPS Spreadsheet and enter your start and end date-times in separate cells.
- 2. Input the calculation formula: Select an empty cell and input your subtraction formula, such as =B1-A1 or =(B1-A1)*24.
- 3. Format the result: Right-click the cell, select 'Format Cells', and apply the custom format [h]:mm or format as a General number for decimal hours.
Frequently Asked Questions
Why does my time calculation show a string of hash symbols (####)?
This usually happens when you subtract a later time from an earlier time without including the date, resulting in a negative time value. Excel cannot display negative times under the default 1900 date system, resulting in the #### error.
How do I calculate time differences crossing midnight if only the time is entered without a date?
If you only have times (e.g., 10:00 PM to 6:00 AM), use the MOD function to handle the midnight crossover. The formula =MOD(End_Time - Start_Time, 1) will yield the correct duration even without date values.
Can I calculate the time difference in total minutes instead of hours?
Yes. Instead of multiplying by 24, you can multiply the subtracted time difference by 1440 (since there are 1,440 minutes in a 24-hour day). For example, use =(B1-A1)*1440 and format the result cell as General.
