When you add time in Excel, the results are usually displayed in the standard 24-hour clock format. This works fine for normal times of day, but it creates problems when the total goes beyond 24 hours. For example, if you add 12 hours and 18 hours, Excel may show 06:00 instead of 30:00 because it resets the clock after a full day.
This can be frustrating when you’re calculating employee shifts, tracking machine run time, or measuring project durations that last longer than a single day. The solution is simple. Use the right custom formatting so Excel can display totals over 24 hours correctly.
In this guide, we’ll walk through step-by-step methods to add time in Excel over 24 hours using formulas and formatting.
Here’s how to add time in Excel over 24 hours:
➤ Open your dataset in Excel.
➤ Click on cell D2 and enter the following formula:
=B2+C2
➤ Press Enter.
➤ Drag the fill handle down to copy the formula for the rest of the rows.
➤ Select the Total Time column, for example, cell range D2:D11.
➤ Right-click and choose Format Cells.
➤ Go to the Number tab and select Custom.
➤ In the Type box, enter: [h]:mm
➤ Click OK. Now Excel will display totals properly, such as 26:30 or 34:15 instead of resetting them back to the 24-hour clock.
![Overview of adding time in Excel over 24 hours with the [h]:mm format](https://excelinsider.com/wp-content/uploads/2025/10/1-adding-time-in-excel-over-24-hours.png)
Add Time in Excel Over 24 Hours Using Formula and Custom Format
In the following dataset, we are tracking employee work hours across shifts. Column A lists the Employee Names, Column B contains their Start Times, Column C has Hours Worked, and Column D will show the Total Time after adding the hours. Because column B holds a time of day, each result in column D is the point where the shift ends, counted from midnight of the start day. For example, 26:30 means the shift ends at 2:30 the next morning. To add a set number of hours to a time with a formula, see how to add hours, minutes and seconds in Excel.
We’ll use this dataset to demonstrate how to add time in Excel over 24 hours.
The most reliable way to add time in Excel that exceeds 24 hours is by combining a simple addition formula with a custom number format. The formula calculates the total correctly, and the format ensures Excel displays the result without resetting after 24 hours.
Step 1: Add Time with a Formula
Let’s calculate total time for each employee by adding the Start Time and Hours Worked.
Here’s how you can do it:
➤ Open your dataset in Excel.
➤ Click on cell D2 and enter the following formula:
=B2+C2
➤ Press Enter. Excel will return a result, but if the total is more than 24 hours, it will reset. For example, 26 hours may appear as 02:00.
➤ Drag the fill handle down to copy the formula for the rest of the rows. At this point, the totals are calculated correctly but not displayed in the right format.
Step 2: Apply the [h]:mm Custom Number Format
To display results beyond 24 hours, we need to apply a custom format.
Here’s how to do it:
➤ Select the Total Time column, for example, cell range D2:D11.
➤ Right-click and choose Format Cells.
➤ Go to the Number tab and select Custom.
➤ In the Type box, enter: [h]:mm
➤ Click OK.
![Custom number format [h]:mm entered in the Type box](https://excelinsider.com/wp-content/uploads/2025/10/7-adding-time-in-excel-over-24-hours.png)
➤ Now Excel will display totals properly, such as 26:30, 27:15, or 34:15, instead of resetting them back to the 24-hour clock.
Sum Multiple Time Values Over 24 Hours
Sometimes you need to total several time values instead of adding hours to a single start time. For example, you may want to know the total number of hours worked by all employees in a week or across shifts. In Excel, you can use the SUM function for this, but you must apply the custom format to show results beyond 24 hours.
Here’s how to do it:
Step 1: Use the SUM Function to Add Total Time
➤ Open your dataset in Excel.
➤ Click on cell E2 and enter the following formula:
=SUM(C2:C11)
➤ Press Enter. Excel will calculate the sum of all hours worked in Column C. But if the total exceeds 24 hours, it may display only the remainder. For example, 2:50 instead of 218:50.
Step 2: Apply Custom Format
➤ Select cell E2.
➤ Right-click and choose Format Cells.
➤ Go to the Number tab and click Custom.
➤ In the Type box, enter: [h]:mm
➤ Click OK.
![[h]:mm custom format applied to the SUM result](https://excelinsider.com/wp-content/uploads/2025/10/12-adding-time-in-excel-over-24-hours.png)
➤ Now Excel will correctly display the grand total of hours worked, such as 218:50, instead of resetting the clock after every 24 hours.
![Grand total of 218:50 hours worked displayed with [h]:mm](https://excelinsider.com/wp-content/uploads/2025/10/13-adding-time-in-excel-over-24-hours.png)
Downloadable Resources
Get our practice workbook or necessary files that we have worked with to prepare this article.
Add Time in Excel Over 24 Hours.xlsx
Frequently Asked Questions
Why does Excel reset time after 24 hours?
Excel represents time as part of a day, where 1 equals 24 hours, 0.5 equals 12 hours, and so on. When you add up time values, Excel still interprets them within a 24-hour cycle.
That’s why 26 hours appears as 02:00. Excel shows the remainder after one full day. The calculation itself is correct, but the display format needs to be changed to show the full total.
What format should I use to display more than 24 hours?
The most reliable format is [h]:mm. The square brackets around h are important because they tell Excel to keep accumulating hours instead of resetting at 24. Without brackets, Excel will treat the result as clock time. If your times include seconds, use [h]:mm:ss. To show the total in minutes instead, use [mm]:ss, which displays 26:30 as 1590:00.
Can I show both days and hours in my result?
Yes, for totals under 32 days. Use the custom format d "day" h:mm, which shows 34:30 as 1 day 10:30. The d code shows the day of the month, so it starts again after 31 days. For longer totals, use =INT(D2)&" days "&TEXT(D2,"h:mm"), which returns text such as 45 days 10:30. Excel has no format code for weeks or months of duration.
Wrapping Up
Adding time in Excel over 24 hours is easy once you know how. The key is to use a normal formula for calculation and then apply the [h]:mm format so Excel shows the full result without resetting the clock.
This method works for adding two times, summing a list of hours, or tracking long shifts and projects. To find the time between two date-times that are more than a day apart, see how to calculate elapsed time between two dates in Excel. With the right format, Excel can handle totals of dozens or even hundreds of hours and display them clearly.
