Hi!

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


=INT(J33-D33)&" days "&TEXT(J33-D33,"h"" hrs ""m"" mins """)

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

Here's the spreadsheet I'm working on


Average Time in Days & Hours.xlsx - Google Drive


Any help would be most appreciated

Thanks

In L3 enter =J3-D3 then create a custom format like dd "days" hh "hrs" mm "mins"
Now you get "12 days 13 hrs 30 mins" BUT the underlying value is still a number
Pull down as required

Average as usual and apply the same custom format


BTW please post your sheet on the board. External links are unsafe and not everyone can access them

Attached Files

Copy of Average Time in Days & Hours.xlsx (24.8 KB, 7 views)