# Time Average Calculation

• Hi,
I’m sorry for bothering you a lot with this file
Hopefully it’ll be the last time

Now
Referring to “Staff Members Performance” sheet
I’m trying to find the average
With the formula I’m supposed to get 15 minutes (or 0:15), not the value displayed on D6

I’ve tried another way which is transferring the time values of G column first to minutes by multiplying with 1440, however this is not working for the nature of the generated report of the system which is generating the timing with the date 1/1/1970 (kindly check sheet “1”)

What I could do with this please?

## Files

• Hello,

You can use the following formula :

Code
``=AVERAGEIF(G3:G7,">0")``

Hope this will help

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Well, I'm afraid the formula isn't that accurate
you see, it's supposed to end up with 15 minutes
or 0:15

you got 37 minutes

• Hello again,

Hope this will help

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Yeah, this works perfectly
last request though
any idea how to convert the format to be 0:15, not just 15?

For your latest request ... just divide your result by 1440 ... and use the appropriate hh::mm custom format ...

In cell D8, your formula could be :

Code
``=((HOUR(D6)*60+MINUTE(D6))/COUNTA(G3:G7))/1440``

Hope this helps

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Yeah, that's perfect
thanks a million

• Glad you have managed to completely sort it out ...:wink:

Thanks a lot ... for your Thanks ... AND for the Like ..:smile:

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Why do you keep posting in the VBA section? There is a forum section called Excel Formulas where you should be posting.

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!