# Calculate total Days / Hours / Minutes between 2 dates , then average

#### Money#1

##### New Member
Hi, I've been using the below formula to give me total days, hours and minutes between two dates, this will deliver a result similar to <1 Days 1 hrs 23 mins>

=IFERROR(INT(E195-D195)&" Days "&TEXT(E195-D195," H "" hrs ""m"" mins """),0)

I then use the below formula to calculate total, however what I'd really like next is the average days hours and minutes across the range, any suggestions about changing the formula to accommodate?

=INT(SUMPRODUCT(H2:H195-G2:G195))&" days "&TEXT(SUMPRODUCT(H2:H195-G2:G195),"h"" hrs ""m"" mins """)

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### jasonb75

##### Well-known Member
You could try changing the two instances of SUMPRODUCT in the second formula to AVERAGE.

If you're not using office 365 with dynamic arrays then you will most likely need to array confirm it with Ctrl Shift Enter.

#### Money#1

##### New Member
You could try changing the two instances of SUMPRODUCT in the second formula to AVERAGE.

If you're not using office 365 with dynamic arrays then you will most likely need to array confirm it with Ctrl Shift Enter.
Thanks, I'd already tried that but unfortunately to no prevail

#### jasonb75

##### Well-known Member
Because your range was quite specific (rows 2 to 195) I did assume that they all have data in them, if any are empty then it will skew the results as the empty rows will have a value of zero.

If that is the case then you will either need to use a dynamic range to omit the empty rows, or use a more complex formula.

For an alternative formula please update your forum profile to show which version of excel you are using by clicking your user name at the top right of this page, then going to 'Account details'
Scroll down and tick the correct box, then continue to the bottom and save changes.

#### Money#1

##### New Member

Because your range was quite specific (rows 2 to 195) I did assume that they all have data in them, if any are empty then it will skew the results as the empty rows will have a value of zero.

If that is the case then you will either need to use a dynamic range to omit the empty rows, or use a more complex formula.

For an alternative formula please update your forum profile to show which version of excel you are using by clicking your user name at the top right of this page, then going to 'Account details'
Scroll down and tick the correct box, then continue to the bottom and save changes.
Thank you, didn't realise that was there, profile updated.

But yes, as you suggested all referenced cells contain data, for which it is all in one date/time format. The full workbook is much larger, the cells I selected were for illustration purposes and to test possible options on a smaller data set.

Thanks

#### jasonb75

##### Well-known Member
Just to be certain, try this one instead. Will need array confirming for Excel 2019.
Excel Formula:
``=INT(AVERAGE(IF(H2:H195<>"",H2:H195-G2:G195)))&" days "&TEXT(AVERAGE(IF(H2:H195<>"",H2:H195-G2:G195)),"h"" hrs ""m"" mins """)``
In my opinion, this returns the correct results while ignoring empty rows (if any).
If you think that the result is still incorrect then I would need a reference expected average to work from. What would you expect the average to be from the table below?
Total in the third column is 88 days, 9 hrs, 34 mins
 04-Mar-2021 04:17:55 15-Mar-2021 09:22:55 11 Days 5 hrs 5 mins 08-Mar-2021 13:20:04 17-Mar-2021 21:02:40 9 Days 7 hrs 42 mins 09-Mar-2021 11:35:41 19-Mar-2021 22:53:53 10 Days 11 hrs 18 mins 03-Mar-2021 23:09:48 14-Mar-2021 15:54:42 10 Days 16 hrs 44 mins 11-Mar-2021 01:31:06 21-Mar-2021 20:08:04 10 Days 18 hrs 36 mins 08-Mar-2021 09:26:56 13-Mar-2021 23:46:13 5 Days 14 hrs 19 mins 07-Mar-2021 15:41:34 15-Mar-2021 13:35:10 7 Days 21 hrs 53 mins 11-Mar-2021 03:28:40 17-Mar-2021 02:18:26 5 Days 22 hrs 49 mins 05-Mar-2021 23:05:57 12-Mar-2021 07:29:51 6 Days 8 hrs 23 mins 08-Mar-2021 02:09:15 18-Mar-2021 04:49:15 10 Days 2 hrs 40 mins

#### Money#1

##### New Member

HI,
Just to be certain, try this one instead. Will need array confirming for Excel 2019.
Excel Formula:
``=INT(AVERAGE(IF(H2:H195<>"",H2:H195-G2:G195)))&" days "&TEXT(AVERAGE(IF(H2:H195<>"",H2:H195-G2:G195)),"h"" hrs ""m"" mins """)``
In my opinion, this returns the correct results while ignoring empty rows (if any).
If you think that the result is still incorrect then I would need a reference expected average to work from. What would you expect the average to be from the table below?
Total in the third column is 88 days, 9 hrs, 34 mins
 04-Mar-2021 04:17:55 15-Mar-2021 09:22:55 11 Days 5 hrs 5 mins 08-Mar-2021 13:20:04 17-Mar-2021 21:02:40 9 Days 7 hrs 42 mins 09-Mar-2021 11:35:41 19-Mar-2021 22:53:53 10 Days 11 hrs 18 mins 03-Mar-2021 23:09:48 14-Mar-2021 15:54:42 10 Days 16 hrs 44 mins 11-Mar-2021 01:31:06 21-Mar-2021 20:08:04 10 Days 18 hrs 36 mins 08-Mar-2021 09:26:56 13-Mar-2021 23:46:13 5 Days 14 hrs 19 mins 07-Mar-2021 15:41:34 15-Mar-2021 13:35:10 7 Days 21 hrs 53 mins 11-Mar-2021 03:28:40 17-Mar-2021 02:18:26 5 Days 22 hrs 49 mins 05-Mar-2021 23:05:57 12-Mar-2021 07:29:51 6 Days 8 hrs 23 mins 08-Mar-2021 02:09:15 18-Mar-2021 04:49:15 10 Days 2 hrs 40 mins
Hi, thanks again, as there are 10 lines (if I've counted correctly) I'd expect the result to be 8 days 20 hours 9 minutes

#### jasonb75

##### Well-known Member
That was the result that I had from the formula in the post and when I used your formula with AVERAGE instead of SUMPRODUCT (with the range reduced from row 195 to row 11).

If it is giving you an unexpected result then there must be something in the data that is causing it.

The data must be valid date / time entries otherwise you would be seeing #VALUE! errors instead of any result.

#### Money#1

##### New Member
Thanks, it appears that if I select an even smaller array it works, so something isn't correct with my data set.

Replies
5
Views
126
Replies
8
Views
114
Replies
3
Views
221
Replies
6
Views
578
Replies
1
Views
70

1,129,879
Messages
5,638,800
Members
417,053
Latest member
SaturdayNight

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back