Dividing hours (time) to get a percentage

[mpantalo]

Hi,

I have formatted my cells [hh]:mm to display a number of hours. I'm trying to work out a percentage of two cells both displayed as [hh]:mm. i.e. 1275:55 divided by 237986:33 * 100 to give me a percentage the 1275 hours is of the 237986 hours. I keep getting #VALUE!

Hope this makes sense. Can anyone help?

 

[zzjasonzz]

use =hour() to extract just the hour

=hour(A1) etc

 

[debooo]

i know this cannot possibly be the correct way to do this, but if you do not format them as time and instead format them as text (simply type '1275:00 and '237986:33) into the cells the below formula would work, but like i said its probably much more complicated then necessary.

=((RIGHT(E7,2)/60)+(LEFT(E7,LEN(E7)-3)))/((RIGHT(E8,2)/60)+(LEFT(E8,LEN(E8)-3)))*100

i put the 1275 in e7 and the 237986 in e8 and the result i get is

0.536129737864038

 

[blazonQC]

=((HOUR(A1) * 60) + MINUTE(A1)) / ((HOUR(B1) * 60) + MINUTE(B1))

where b1 = 237986:33
and a1 = 1275:55

don't multiply by 100 at the end, just format the cells for percentage

i'm not too good with formulas.... but that worked for me.

Chad

 

[Brian from Maui]

Hi,

I have formatted my cells [hh]:mm to display a number of hours. I'm trying to work out a percentage of two cells both displayed as [hh]:mm. i.e. 1275:55 divided by 237986:33 * 100 to give me a percentage the 1275 hours is of the 237986 hours. I keep getting #VALUE!

Hope this makes sense. Can anyone help?

The largest time Excel recognizes is 9999:59:59. That's the reason for the value error.