Calculating time over 10000 hours

[SDuque]

I am a trying to figure out a percentage of time and running into trouble when the total number of hours is more than 10000. For example, I have trucks out of service for 10456:15:48 (sum of hours from each truck over a month). The total possible hours of service is 26000:00:00. It won't calculate any math, such a percentage of out of service hours, with hours over 10000 ... ideas? Thank you.

 

[diddi]

hi and welcome to MrExcel
i suggest you convert individual truck data to decimal, there should be no reason why excel cant manage a floating point calculation such as what you need in decimal

 

[joeu2004]

I have trucks out of service for 10456:15:48 (sum of hours from each truck over a month). The total possible hours of service is 26000:00:00. It won't calculate any math, such a percentage of out of service hours, with hours over 10000 ... ideas?

We cannot enter times greater than 9999:59:59.999.

But we can calculate and display times up to 71003183:59:59.499 formatted as Custom [h]:mm:ss, and up to 71003183:59:59.999 formatted as Custom [h]:mm:ss.000 .

(FYI, 71003160 corresponds to the number of hours in 2958465 days, which is the "serial number" of the max date 12/31/9999.)

So there should be no problem calculating a sum of hours that is displayed as 10456:15:48, as demonstrated below.

To enter time constants greater than or equal to 10000 hours, enter the formula =hours/24, formatted as Custom [h]:mm:ss . For example, =26000/24, as demonstrated below.

(Alternatively, we could enter the constant 12/18/1902 8:00, formatted as Custom [h]:mm:ss, having discovered that INT(26000/24) is 1083, the "serial number" for the date 12/18/1902. Not really recommended.)

Then time calculations (e.g. including percentage of out of service hours) work just fine, as demonstrated below.

Book1
	A	B
1	out of srvc hrs
2	5456:15:48	entered as formatted
3	5000:00:00
4
5	10456:15:48	total out of srvc hrs
6	26000:00:00	total srvc hrs
7	40.22%	pct out of srvc hrs
8
9	max time calc
10	71003160:00:00.000	entered 12/31/9999
11	23:59:59.999	entered as formatted
12	71003183:59:59.999	sum
13
14	71003183:59:59.999	entered 12/31/9999 23:59:59.999
Sheet1

Select cells or hover cursor over cells to see formulas.