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

Money#1

New Member
Joined
Jan 25, 2012
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Money#1

New Member
Joined
Jan 25, 2012
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 25, 2012
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
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:5515-Mar-2021 09:22:5511 Days 5 hrs 5 mins
08-Mar-2021 13:20:0417-Mar-2021 21:02:409 Days 7 hrs 42 mins
09-Mar-2021 11:35:4119-Mar-2021 22:53:5310 Days 11 hrs 18 mins
03-Mar-2021 23:09:4814-Mar-2021 15:54:4210 Days 16 hrs 44 mins
11-Mar-2021 01:31:0621-Mar-2021 20:08:0410 Days 18 hrs 36 mins
08-Mar-2021 09:26:5613-Mar-2021 23:46:135 Days 14 hrs 19 mins
07-Mar-2021 15:41:3415-Mar-2021 13:35:107 Days 21 hrs 53 mins
11-Mar-2021 03:28:4017-Mar-2021 02:18:265 Days 22 hrs 49 mins
05-Mar-2021 23:05:5712-Mar-2021 07:29:516 Days 8 hrs 23 mins
08-Mar-2021 02:09:1518-Mar-2021 04:49:1510 Days 2 hrs 40 mins
 

Money#1

New Member
Joined
Jan 25, 2012
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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:5515-Mar-2021 09:22:5511 Days 5 hrs 5 mins
08-Mar-2021 13:20:0417-Mar-2021 21:02:409 Days 7 hrs 42 mins
09-Mar-2021 11:35:4119-Mar-2021 22:53:5310 Days 11 hrs 18 mins
03-Mar-2021 23:09:4814-Mar-2021 15:54:4210 Days 16 hrs 44 mins
11-Mar-2021 01:31:0621-Mar-2021 20:08:0410 Days 18 hrs 36 mins
08-Mar-2021 09:26:5613-Mar-2021 23:46:135 Days 14 hrs 19 mins
07-Mar-2021 15:41:3415-Mar-2021 13:35:107 Days 21 hrs 53 mins
11-Mar-2021 03:28:4017-Mar-2021 02:18:265 Days 22 hrs 49 mins
05-Mar-2021 23:05:5712-Mar-2021 07:29:516 Days 8 hrs 23 mins
08-Mar-2021 02:09:1518-Mar-2021 04:49:1510 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
Joined
Dec 30, 2008
Messages
12,637
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 25, 2012
Messages
10
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Thanks, it appears that if I select an even smaller array it works, so something isn't correct with my data set.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,687
Messages
5,637,830
Members
416,984
Latest member
dee10

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top