# Need to calculate average of hours worked

1. ## Need to calculate average of hours worked

Hello,
I'm using Office 2010 and I want to calculate the average of hours worked for about 40 employees on a monthly basis. The employees all work from about 160 to about 200 hours. My fields are formatted as time but when I use average I always get a #Div/0 error. How do I correct this?

Examples

Agent name Total shift time
(hh:mm:ss)
Jacqueline 140:17:26
Julio 202:41:34
Wendy 178:56:11
Rosa 194:49:04
Cesar 206:09:47
Stephanie 152:05:47
Cecilia 158:06:00
Victoria 155:56:51

When I use average I get Div/) error or an average like 21 hours which looks like it cuts off the first digit of the hours (150 hours becomes 50 hours?)

2. ## Re: Need to calculate average of hours worked

Maybe I'm unaware of something, but HH:MM:SS is for a time on a clock....not an amount of time between two times (which is what you have in your list). To find the average, you'll have to convert your HH:MM:SS to a decimal, then average. I did it in the attached file with all my steps laid out column by column....then I put everything into one formula in the last column...one LONG formula. Here's the final formula....

=LEFT((RIGHT(A3,(LEN(A3))-(FIND(" ",A3,1)))),3)+(((MID((RIGHT(A3,(LEN(A3))-(FIND(" ",A3,1)))),5,2))/60)+((RIGHT((RIGHT(A3,(LEN(A3))-(FIND(" ",A3,1)))),2))/6000))

HAHA! That's long. There's no way I would have known that without laying it out column by column like on the spreadsheet So just ignore that formula and look at the spreadsheet

Here it is:Convert Time.xlsx

3. ## Re: Need to calculate average of hours worked

Thank you for the help!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1