I'm trying to build a report that shows the callers percent of time (during
an 8 hour day) they spend on the phone. I have the callers total time for
the Month and I need to calculate two things:

Average amount of time spent on the phone per day (=Total Time/# Days worked)

Average % of time spent on the phone per day is where I'm confused. I
thought I would just divide =((Total Time/# Days worked)/8) but I'm getting
some strange results. For example, I have an Average time of 2:23:11, so
just divide that number by 8 and I got 1.24%. What am I doing wrong?

You assumed that Excel time is measured in hours, but it's measured in days,
so you need to multiply your answer by 24.
--
David Biddulph

"FrankTimJr" wrote in message
...
I'm trying to build a report that shows the callers percent of time
(during
an 8 hour day) they spend on the phone. I have the callers total time for
the Month and I need to calculate two things:

Average amount of time spent on the phone per day (=Total Time/# Days
worked)

Average % of time spent on the phone per day is where I'm confused. I
thought I would just divide =((Total Time/# Days worked)/8) but I'm
getting
some strange results. For example, I have an Average time of 2:23:11, so
just divide that number by 8 and I got 1.24%. What am I doing wrong?

Thank you!!

"David Biddulph" wrote:

You assumed that Excel time is measured in hours, but it's measured in days,
so you need to multiply your answer by 24.
--
David Biddulph

"FrankTimJr" wrote in message
...
I'm trying to build a report that shows the callers percent of time
(during
an 8 hour day) they spend on the phone. I have the callers total time for
the Month and I need to calculate two things:

Average amount of time spent on the phone per day (=Total Time/# Days
worked)

Average % of time spent on the phone per day is where I'm confused. I
thought I would just divide =((Total Time/# Days worked)/8) but I'm
getting
some strange results. For example, I have an Average time of 2:23:11, so
just divide that number by 8 and I got 1.24%. What am I doing wrong?

Try

=(AverageTime*24)/8

You could also write the formula as

=AverageTime/(8/24)

Excel stores times as a fraction of a 24-hour day (6:00 = 0.25, 12:00
= 0.5, 18:00 = 0.75, etc), so if you are doing arithmetic with the
number of hours (as in your value of 8 for hours/day), you need to
multiply by 24.

Format the cell for Percentage.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Fri, 16 Jan 2009 14:14:00 -0800, FrankTimJr
wrote:

I'm trying to build a report that shows the callers percent of time (during
an 8 hour day) they spend on the phone. I have the callers total time for
the Month and I need to calculate two things:

Average amount of time spent on the phone per day (=Total Time/# Days worked)

Average % of time spent on the phone per day is where I'm confused. I
thought I would just divide =((Total Time/# Days worked)/8) but I'm getting
some strange results. For example, I have an Average time of 2:23:11, so
just divide that number by 8 and I got 1.24%. What am I doing wrong?

To calculate the average percentage of time spent on the phone per day, follow these steps:

1. Convert the average amount of time spent on the phone per day to a decimal. For example, if the average time spent on the phone per day is 2 hours and 23 minutes, convert it to 2.38 (
   Formula:

   2 + 23/60 

   ).
2. Divide the decimal value by 8 (total hours available in a day) and multiply by 100 to get the percentage. For example, if the decimal value is 2.38, divide it by 8 and multiply by 100 to get 29.75%.

So, the average percentage of time spent on the phone per day for the caller in this example is 29.75%.

To double-check your calculation, you can also calculate the total time spent on the phone as a percentage of the total time available in the month (assuming 20 working days in a month). If the total time spent on the phone is 100 hours in a month, the total time available is 160 hours (20 days x 8 hours per day). Dividing 100 by 160 and multiplying by 100 gives you 62.5%, which means the caller spent 62.5% of their time on the phone during the month. Dividing this by 20 (number of working days) gives you 3.125%, which is close to the 2.38/8 x 100 calculation we did earlier.