Solved Need EXCEL formula to find an average of days hours minutes

December 18, 2015 at 09:16:09
Specs: Windows 7
I have used
=INT(B5-A5)&" Days "&HOUR(MOD(B5-A5,1))&" Hour "&MINUTE(MOD(B5-A5,1))&" Minutes"
to find the days, hours and minutes between 2 dates.
Now I need to find the average of all the days, hours and minutes. I'm having a hard time coming up with an AVERAGE formula that will work with my previous formula. Please help!

See More: Need EXCEL formula to find an average of days hours minutes

Report •


#1
December 18, 2015 at 10:41:33
✔ Best Answer
I assume that you are using your formula in more than 1 cell and coming up with "Days, Hours, Minutes" for multiple pairs of dates/times. You then want to average those results.

If that is the case, then I started with pairs of dates/times in A5:B5, A6:B6, A7:B7, A8:B8 and used your formula in C5:C8.

I then used this formula to average the values in C5:C8:

=SUMPRODUCT(LEFT(C5:C8,2)*1)/COUNTA(C5:C8)&" Days " &
SUMPRODUCT(MID(C5:C8,FIND("s",C5:C8)+1,3)*1)/COUNTA(C5:C8) & " Hours " &
SUMPRODUCT(MID(C5:C8,FIND("r",C5:C8)+1,3)*1/COUNTA(C5:C8))&" Minutes"

It worked for my example data but you may have to modify to work for yours. How many characters you extract with your MID and LEFT functions may need to be changed.

If you can't get it to work, you'll need to post a short example of your exact data.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
December 18, 2015 at 11:00:25
That worked perfectly! Thank you! How do I get it to round to a whole number or maybe only one decimal?

Report •

#3
December 18, 2015 at 11:40:02
Just wrap one of the ROUND functions around each of the sections that produce the numbers:

=ROUND(SUMPRODUCT(LEFT(C5:C8,2)*1)/COUNTA(C5:C8),1) & ....

ROUND, ROUNDUP, ROUNDDOWN

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question