How to calculate more than 9999:00:00 hours?

Excel has a limit using the [h]:mm:ss format and 9999 is the maximum hours that Excel is able to calculate with any function. Example; it is not possible to summarize 10000:00:00 and 10000:00:00.

I am very grateful if anyone has a workaround for this issue. Thanks in advance.

Hi,

Googling ... I found this info
http://www.pcmag.com/article2/0,2817,2345762,00.asp

HTH

Originally Posted by DonkeyOte

The limitation re: 9999:59:59 applied to manual entry not calculation but yes it follows that if you have manually entered a value >= 10000:00:00 then XL can't convert that to time and will display as string.

http://support.microsoft.com/kb/214386

Realistically you need to adjust your data entry - ie enter as days.decimaldays eg for 12000:30:59

Please Login or Register  to view this content.

against which you can apply the [h]:mm:ss format so displays as 12000:30:59

and you can then calculate off the underlying value which is a numeric value (500.021516...)

If you can't adjust the input strings you're looking at cumbersome formulae, eg:

Please Login or Register  to view this content.

Thanks. Now all my time strings are numbers, but I still can't use sum() for calculating the sum. Strange because the correct sum is showing up in the Excel number bar at the bottom of the Excel frame in the right corner

Originally Posted by DonkeyOte

Could you post a sample file illustrating the problem at hand ?

Forget the last post. I found that I had the wrong format for the cell I where I calculate the sum. Now it works! Thanks again.