+ Reply to Thread
Results 1 to 3 of 3

Need to calculate average of hours worked

  1. #1
    Registered User
    Join Date
    10-13-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    2010
    Posts
    99

    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?)

    Please help

  2. #2
    Forum Contributor
    Join Date
    07-25-2012
    Location
    Winterville, NC
    MS-Off Ver
    Excel 2013
    Posts
    141

    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
    Last edited by acroley1; 05-26-2015 at 10:12 PM.


    If this post was helpful or at least brought a smile to your face , please click the Add Reputation button as a way to say thank you!!!!!!

  3. #3
    Registered User
    Join Date
    10-13-2014
    Location
    Phoenix, AZ
    MS-Off Ver
    2010
    Posts
    99

    Re: Need to calculate average of hours worked

    Thank you for the help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 05-22-2015, 07:27 AM
  2. Replies: 2
    Last Post: 01-17-2014, 02:22 PM
  3. Replies: 4
    Last Post: 02-12-2013, 07:01 AM
  4. Replies: 2
    Last Post: 02-11-2013, 02:26 PM
  5. [SOLVED] Average hours worked based on days worked during a pay cycle
    By jaycee66 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-14-2012, 06:27 PM

Bookmarks

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