add 10,0015:15 hours to 03:53 Hours to a total time

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

when adding a hour number larger that 10,000 to another time number the
formula will not give a correct answer. Tried several time formats and
nothing worked. I need a format that is (HH,HHH:MM).
 
Unfortunately 10,000 hours is an Excel limit, you have to find a workaround
for handling greater number of hours (e.g. work with plain numbers).

Regards,
Stefi

„Alan†ezt írta:
 
As Stefi mentioned, you've run into a type of limit. XL doesn't recognize
"10000:15" as being a time value. However, you can do the mathematical
equivalent of

="3:00"+10000/24+15/(24*60)

with the middle term being your hours and last term being minutes.To get the
format you desire, you'll have to *fake* it, and use a formula similar to:

=TEXT(INT(A2*24),"#,###")&":"&MINUTE(A2)
where A2 is the cell containing previous formula.

the problem with this is you now have a text string as opposed to a number.
 
The largest time value that can be *manually* entered in a cell is
9999:59:59.

You can use formulas to return much higher time values, though. So, you'd
need to split your time into 2 cells:

A1 = 5000:00
A2 = 5000:00
A3 = 3:53

=SUM(A1:A3)

Returns 10003:53
 
Back
Top