Hi!

Im trying to calculate the average time elasped in 2 columns in Days, Hours and Minutes format. I have used

=INT(I10-H10)&" Days, "&HOUR(I10-H10)&" Hours, "&MINUTE(I10-H10)&" Minutes"



to get the time elapsed. However, when I try to get the average in the column I get either #DIV/0 or #Value! error message.

Some of the time elapsed goes over 30 days so just using I10-H10 doesnt give the correct answer in those instances.

Help!

Copy of some of the document below.

ORDERED DATEFILMED DATEREPORT DATEOrd-FilFil-Sig02/07/2015 23:1003/07/2015 10:1603/07/2015 12:040 Days, 11 Hours, 6 Minutes0 Days, 1 Hours, 48 Minutes03/07/2015 07:4403/07/2015 10:5403/07/2015 13:060 Days, 3 Hours, 10 Minutes0 Days, 2 Hours, 12 Minutes03/07/2015 11:4503/07/2015 12:1903/07/2015 13:100 Days, 0 Hours, 34 Minutes0 Days, 0 Hours, 51 Minutes02/07/2015 22:1302/07/2015 22:5403/07/2015 13:160 Days, 0 Hours, 41 Minutes0 Days, 14 Hours, 22 Minutes01/07/2015 22:1102/07/2015 03:1003/07/2015 14:080 Days, 4 Hours, 59 Minutes1 Days, 10 Hours, 58 Minutes

Thanks in advance!

Why are you doing it that way instead of just using a custom number format? Since you're turning your calculation into text, there is no easy way to turn it back into a number for averaging. The only way around that would be to use a formula that goes back to the inputs, not the outputs.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

I reckoned i was over complicating things a bit! Have spent a couple of days online trying to solve the problem!

The main problem is when I try to subtract one date/time from another date/time in the same month, the answer is right.

When I subtract one date/time from another date/time in different months/years, the answer is way wrong.

Then each month I have to get an average turnaround time of each column - and this is when I get the error message....

I had already custom formated the columns as "dd" Days "hh" Hours "mm" mins - should I change this?

I Had originally thought when I started getting these stats it would be a simple A2-A1 to get the difference and then use the AVERAGE() to get the average time - but nothing is ever simple with excel I have discovered!!

Where the differences are up to 31 days, you can use simple subtraction and average, with a custom number format like:
d" days, "h" hours & "m" minutes"
For anything more than a month, you'd probably do best to work with a simply numeric format that expresses the output in decimal days, rather than trying to get it to do days, hours & minutes.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Ok, so when I do that is the answer i am seeing (2291.07) in hours? Is there a way to change that to days, hours and minutes without getting a wrong answer?

Head completly wrecked over this!

Thanks.

I can't see where you'd get 2291.07 from using the data you posted. That said, if you used a simple subtraction, the 2291.07 is 2291.07 days.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi,

I found a solution on another website using the A2-A1 formula and custom formating the answer as [h]:mm I was able to get my answer in hours and minutes format.

The formula I found to change the sum total of hours and mins to days, hours and mins was (assuming my answer was in cell A25)

=INT(A25) &" Days" & INT(MOD(A25,INT(A25))*24) &" Hours and " & MINUTE(A25) & "Minutes"

So with this in mind, is there a way to further change this answer to Months, days, hours and minutes or am I trying to do the impossible with Excel?!

Thanks.

The formula you're using returns a result that is text, not a number, which makes it very difficult to do any further calculations with it. Besides which, there is no such thing as a standard 'month', so you can't just divide the number of days by a constant and expect a reliable result. That said, you could use the DATEDIF function to express the number of days as calendar months - but only if you have both the starting date and the end date to which the number (2291.07 - which is actually more than 8.6 years) relates.

Since none of the data from your first post spans even one week, it's difficult to understand what it is you're having issues with on that score. Perhaps you should attach a workbook to a post with some representative data. You can do that via the paperclip symbol on the Advanced posting menu.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Post #7 seems to indicate that you have posted the same question on another forum?

If so, please post a link to that forum and read http://www.excelguru.ca/content.php?184 to see why.
Thank you

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks for your help but I realised I was using SUM() instead of AVERAGE(), that was why the answer was so big! Finally sorted!

Thanks for all your help.