# Dividing hours (time) to get a percentage

#### mpantalo

##### New Member
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?

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### zzjasonzz

##### Well-known Member
use =hour() to extract just the hour

=hour(A1) etc

#### debooo

##### Board Regular
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

##### Board Regular
Code:
``=((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.

#### Brian from Maui

##### MrExcel MVP
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.

Replies
3
Views
33
Replies
2
Views
243
Replies
3
Views
431
Replies
4
Views
45
Replies
3
Views
297

1,190,958
Messages
5,983,843
Members
439,866
Latest member
jh3268

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back