Trying to add hours in the same Excel cell

Question

I'm adding hours and I've noticed that Excel doesn't recognize hours above 10,000. However, it's still possible to work with hours above that by dividing them into two parts, but this uses many cells (one cell for the first part, one for the second part, and one for the addition). Is there a way to make the addition of hours work in the same cell? For example, =5000:35+7000:15

Answer 1

< Edited...Not what op wanted... >

Answer 2

Hi,

It works fine for me

Answer 3

Excel does not have a data type for time or date the way it does for numbers and text. All dates and times are stored as a normal decimal value. The integer portion of the value represents the number of days from the start of the epoch (January 00,0000). The fractional portion of the value represents the portion of the day past midnight (.25 = 6AM). It is only when you display the value that Excel converts into something easy to read.

When you input a value such as 5000:35, Excel converts this value to the appropriate decimal value (208.357638888889 which is July 26,0000 8:35AM). Excel only recognizes this as time when it contains a single value. Even =1:25+2 fails. Furthermore, it appears this conversion will occur only if the hour portion of the expression < 10000. 9999:59 works fine but 10000:01 is treated a text.

The output conversion routines will handle computed values larger than 10000. If A1 contains 5000:35 (immediately converted to 208.357) and A2 contains 7000:35 (immediately converted to 291.677), the formula =A1+A2 in A3 produces the value 500.035 which is displayed as May 14,0001 12:50AM.

Once the values are in Excel's internal format, the only way to see the actual number of hours is to multiply the value by 24. In the example above, A3*24 produces 12000.83.

Answer 4

Excel can calculate far more than 10,000 hours; the usual problem is the cell format or how the time is entered. You can do the whole addition in one cell by converting the hours and minutes into Excel’s day-based time value and then formatting the result with a custom format [h]:mm so the hours don’t roll over.

=((5000*60+35)+(7000*60+15))/1440

After entering the formula, format the cell as Custom → [h]:mm. This converts each “hours:minutes” value into total minutes, adds them, and converts back to Excel time in a single cell while allowing totals well above 10,000 hours.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin