Calculate Start-to-Stop Time in Excel in Hours and Minutes
Question details
Calculate the elapsed duration between a start time and a stop time and display the result as total hours and minutes.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet tracks task start and stop times, including tasks that may last longer than 24 hours or continue past midnight.
- Observed behavior
- The required result is an elapsed-time value in hours and minutes without resetting the hour count after 24 hours.
Enter valid Excel time values in separate Start and Stop cells, such as 8:30 AM and 5:15 PM. Ensure the result cell is formatted as a duration rather than a standard clock time.
Subtract the Start Time From the Stop Time and Format Total Hours
Use this method when the stop time is later than the start time on the same day or when your entries include dates as well as times.
Excel stores times as portions of a day, so subtracting the start value from the stop value returns the elapsed duration. The custom format [h]:mm keeps counting total hours after 24 hours.
Select the duration cell, such as C2, enter =B2-A2 where A2 contains the start time and B2 contains the stop time, then press Enter.
Right-click the duration cell and select Format Cells. In Excel, choose the Number tab and then select Custom.
In the Type box, enter [h]:mm and select OK. The brackets around h prevent the hour value from restarting at 0 after 24 hours.
Select the formula cell and drag the fill handle down to calculate durations for additional rows.
Use MOD to Calculate a Duration That Crosses Midnight
Use this formula when the start and stop times contain no dates and a task may end after midnight.
Calculate Start and Stop Time Durations in WPS Spreadsheets
WPS Spreadsheets supports the same elapsed-time formulas and custom number formats used for start-to-stop duration tracking. Enter your times, subtract the start value from the stop value, and use [h]:mm to show total hours and minutes clearly.
- 1. Open the worksheet in WPS Spreadsheets: Open WPS Office, select Spreadsheets, and open your .xlsx or .xls time-tracking workbook.
- 2. Add the duration formula: Select the duration cell, type =B2-A2 for same-day or dated entries, or type =MOD(B2-A2,1) for an overnight time-only entry, then press Enter.
- 3. Set the elapsed-time number format: Select the result cells, open Home > Number Format > More Number Formats, choose Custom, and enter [h]:mm.
- 4. Copy the calculation to other rows: Drag the fill handle from the formula cell down the duration column to calculate each task's elapsed time.
Frequently Asked Questions
Why does my Excel duration display as 0:30 instead of 24:30?
The result cell is likely using a clock-time format such as h:mm. Right-click the cell, choose Format Cells > Custom, and use [h]:mm so Excel displays total elapsed hours.
How do I calculate time from 11 PM to 3 AM in Excel?
If the cells contain times only, use =MOD(B2-A2,1), with the 11 PM start time in A2 and the 3 AM stop time in B2. Format the result cell as [h]:mm.
Can Excel calculate a duration that lasts more than one day?
Yes. Enter complete date-and-time values for both the start and stop cells, use =B2-A2, and format the result with [h]:mm. For example, a 49-hour duration displays as 49:00.
How can I remove seconds from an elapsed-time result in Excel?
Open Format Cells > Custom and use [h]:mm instead of a format such as [h]:mm:ss. This hides seconds while retaining total hours and minutes.
