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!

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

That worked perfectly! Thank you! How do I get it to round to a whole number or maybe only one decimal?

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.

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History