Calculation Issues

How to Calculate Total Hours Between Dates and Times in Excel

Huma Ashraf Ch Sep 25, 2026 869 views

Question details

The user wants to calculate the total duration in hours and minutes between two specific date and time values in Excel, ensuring durations exceeding 24 hours display correctly.

How to Calculate Total Hours Between Dates and Times in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking time durations, such as work shifts or project lengths, that span across multiple days and exceed 24 hours.
Observed behavior: Standard time formats reset after 24 hours, failing to show the total elapsed hours. Incorrect formulas or formats can also result in a #### error.

Before you start

Ensure your start and end date/time values are recognized as valid serial dates in Excel, and not stored as plain text.

Solution 1Recommended

Subtract Date/Time and Apply [h]:mm Custom Format

Subtract the start time from the end time and use the custom format [h]:mm to prevent hours from resetting at 24.

Excel inherently treats dates and times as numbers, where one whole day equals 1. By default, the standard time format resets every 24 hours. To display elapsed hours beyond a single day, you must apply a specific custom format using square brackets.

Enter the subtraction formula

In an empty cell, type the formula to subtract the start date and time from the end date and time. For example, if your start time is in A2 and your end time is in B2, type =B2-A2 and press Enter.

Open the Format Cells dialog

Right-click the cell containing your formula result and select 'Format Cells' from the context menu, or press the Ctrl+1 shortcut.

Select Custom category

In the Format Cells dialog box, navigate to the 'Number' tab and choose 'Custom' from the Category list on the left.

Apply the [h]:mm format

In the 'Type' input field, enter [h]:mm exactly as shown. Click 'OK' to apply the formatting. The cell will now display the total duration, even if it exceeds 24 hours.

Subtract Date/Time and Apply [h]:mm Custom Format

Handling #### Errors: If the result displays as ####, try widening the column first. If it persists, ensure you subtracted the start time from the end time. Excel cannot display negative times natively, which happens if you reverse the subtraction order.

WPS Spreadsheet Time Tracking

Effortlessly Calculate Time Durations with WPS Office

WPS Spreadsheet provides seamless calculation of dates and times. You can easily compute durations over 24 hours using identical functions and custom formats as Microsoft Excel, completely free.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing your date and time data.
2. Input the calculation: Select the target cell and enter the formula subtracting the start time from the end time (e.g., =B2-A2).
3. Access Format Cells: Right-click the result cell and select 'Format Cells', or press the Ctrl+1 keyboard shortcut.
4. Set the custom format: Under the Number tab, select 'Custom', enter [h]:mm into the Type field, and click OK to display the total hours.

Easily format cells with [h]:mm to calculate time durations over 24 hours.Fully compatible with Microsoft Excel formulas and custom number formats (.xlsx).Lightweight software with an intuitive, familiar interface.Completely free to use for daily spreadsheet tasks and data tracking.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my time difference show 0:11 instead of 24:11?

This happens because standard time formats are designed to show a specific time of day, so they reset after 24 hours. You must use square brackets in the custom format, like [h]:mm, to instruct the software to display total elapsed hours instead of a time of day.

Can I multiply the total hours by an hourly rate to calculate pay?

Yes, but you need to convert the time value first. Excel stores time as a fraction of a 24-hour day. To calculate pay, multiply your time difference by 24 and then by your hourly rate (e.g., =(B2-A2)*24*Rate), and format the resulting cell as a standard Number or Currency.

What does it mean when the formula returns #### even if the column is wide?

A #### error in a wide column usually indicates a negative time value. Check your formula to ensure you are subtracting the earlier start date and time from the later end date and time, and not the other way around.

Excel & Data Problems