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

[Money#1]

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

 

[jasonb75]

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]

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]

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]

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]

Just to be certain, try this one instead. Will need array confirming for Excel 2019.

=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]

HI,

Just to be certain, try this one instead. Will need array confirming for Excel 2019.

=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]

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]

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