# formula

#### cocacola

##### New Member
hi and thanks!

I am needing a formula to reflect a percentage as follows:

i am calculating number of hours on the phone (i.e.: 8:30, (8 1/2 hours) minus number of hours off the phone (i.e. 1:45, (1 hour, 45 minutes). Then i need to show a percentage of time on the phone. How can I get a percentage when my format is in [h]:mm?

thx!

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### Dave Patton

##### Well-known Member
On 2002-10-28 16:57, cocacola wrote:
hi and thanks!

I am needing a formula to reflect a percentage as follows:

i am calculating number of hours on the phone (i.e.: 8:30, (8 1/2 hours) minus number of hours off the phone (i.e. 1:45, (1 hour, 45 minutes). Then i need to show a percentage of time on the phone. How can I get a percentage when my format is in [h]:mm?

thx!

Format your result cell as %

example with your numbers in A2 and A3

=(A3/A2) without formatting gives 0.205882353

or 20.59%

#### cocacola

##### New Member
it's actually like this:

=sum(L9-L28-L30/160)

and i'm not getting the same number as if i were to do the math on the calculator:

the first cell reference is a total number of hours worked, the second subtracting leave taken, the third is time off the phone, then that total is divided by the number of hours scheduled to be worked in the month (160). the answer s/b 45% or there abouts. under the 'number format' i'm getting 3.76 as an answer, under the 'percentage' format, i get #####.

L9=92:21
L28=2:00
L30=18:40

L9 is the total hours (92 hours, 21 minutes), minus total leave (2 hours), minus total off-line hours (18 hours, 40 minutes), divided by 160 hours for a percentage of approximately 44%.

???

#### plettieri

##### Well-known Member
Hi

You probably have to convert your time cells to whole numbers to get a percentage you are looking for.....a good thread and a better understanding of this this works in http://www.mvps.org/dmcritchie/excel/datetime.htm

hope this helps

pll

#### IML

##### MrExcel MVP

A couple things will fix this right up:
1) You don't need the sum, it redundant since you have the operaters in there.
That would be
=L9-L28-L30/160
2) This would first divide L30 by 160 and subtract this and L28 from L9 - not what you want. Add parenthesis like this:
=(L9-L28-L30)/160
3) Excel reads time as fraction of a day, so 160 = 160 days or 3,840 hours. Change this by dividing your number by 24 to turn it into consistent hour. End result:
=(L9-L28-L30)/(160/24)
This message was edited by IML on 2002-10-28 17:32

#### RichardS

##### Well-known Member
=(L9-L10-L11)/L13

Where L13 contains 160:00. Format result as %

Richard

#### cocacola

##### New Member
continuing journey...

question: when i copy a worksheet (entire page) to paste into a new workbook, my formulas are not coming over when I paste. why come?

Replies
5
Views
89
Replies
1
Views
326
Replies
3
Views
87
Replies
5
Views
148
Replies
5
Views
89

1,148,280
Messages
5,745,823
Members
423,981
Latest member
ph1l

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

### Which adblocker are you using?

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

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