Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-20-2021, 05:31 AM
Curious George Curious George is offline Calculate Average Time Elapsed in Days, hours and minutes Windows 10 Calculate Average Time Elapsed in Days, hours and minutes Office 2016
Novice
Calculate Average Time Elapsed in Days, hours and minutes
 
Join Date: Mar 2021
Posts: 1
Curious George is on a distinguished road
Default Calculate Average Time Elapsed in Days, hours and minutes

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
Reply With Quote
  #2  
Old 03-20-2021, 07:01 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Calculate Average Time Elapsed in Days, hours and minutes Windows 7 64bit Calculate Average Time Elapsed in Days, hours and minutes Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
File Type: xlsx Copy of Average Time in Days & Hours.xlsx (24.8 KB, 5 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculate Average Time Elapsed in Days, hours and minutes how to calculate average time aymanharake Excel 6 02-17-2017 04:22 PM
Calculating Average Time across days lizakay Excel 3 11-25-2015 05:16 PM
Calculate Average Time Elapsed in Days, hours and minutes Calculate Average Time Elapsed in Days, hours and minutes Nina46 Excel 9 09-11-2015 02:09 AM
Calculate Average Time Elapsed in Days, hours and minutes Calculate difference by days and hours alysolyman Excel 6 05-13-2013 02:19 AM
Calculate Average Time Elapsed in Days, hours and minutes Calculate elapsed time in Word table? Snvlsfoal Word Tables 1 08-11-2011 05:42 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:32 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft