# Solved Converting two dates into Days and Hours

April 13, 2015 at 08:32:57
Specs: Windows 7
 Im trying to take 2 dates and find the turn around time and then find the MAX and Average out of all the results. Right now im using but the results come out in text and wont let me run a max or average function. =NETWORKDAYS(B3-A3)&" Days "&HOUR(MOD(B3-A3,1))&" Hour "&MINUTE(MOD(B3-A3,1))&" Minutes"Is there any way to get the MAX and also the average from the results of this formula? or if i manually type the hours (96) is there a way to change that into (4 Days 0 Hrs)?

See More: Converting two dates into Days and Hours

April 13, 2015 at 18:56:07
 DerbyDad03, Curious about your opinion on the NETWORKDAYS() function.Using the dates:A3: 1/31/2015 2:30B3: 1/7/2015 19:46and your formula=(NETWORKDAYS(B3,A3)*1440+HOUR(MOD(B3-A3,1))*60+MINUTE(MOD(B3-A3,1)))/1440We get 18.71944444But, since NETWORKDAYS() counts both the Start Date & End Dateand since the Start Date is not a full 24 hours, shouldn't the formula be:=((NETWORKDAYS(B3,A3)-1)*1440+HOUR(MOD(B3-A3,1))*60+MINUTE(MOD(B3-A3,1)))/1440So it returns 17.71944444There is also the idea, that since the End Date is also not a full 24 hours it should be:=((NETWORKDAYS(B3,A3)-2)*1440+HOUR(MOD(B3-A3,1))*60+MINUTE(MOD(B3-A3,1)))/1440So it returns 16.71944444I myself would probably opt for the last formula.MIKEhttp://www.skeptic.com/

#1
April 13, 2015 at 13:05:26
 Are you interested in the Hours/Minutes in your turnaround time, or just Days?Do you have a Daily Start & End Time that needs to be taken into account?To turn 96 hours into Days, just Divide by 24.MIKEhttp://www.skeptic.com/

Report •

#2
April 13, 2015 at 13:08:57
 Are you sure you posted the correct formula? Shouldn't the first part be NETWORKDAYS(B3,A3) not NETWORKDAYS(B3-A3)?Why not use this formula (perhaps in a hidden cell) to determine the turn around time in "days", do your calculations and then reconvert the result back into a format that you like?=(NETWORKDAYS(B3,A3)*1440+HOUR(MOD(B3-A3,1))*60+MINUTE(MOD(B3-A3,1)))/1440e.g.:A3: 1/31/2015 2:30B3: 1/7/2015 19:46Your (corrected) formula returns:18 Days 17 Hour 16 MinutesMy formula returns:18.71944444You can do all sorts of calculations on 18.71944444

Report •

#3
April 13, 2015 at 13:21:20
 I actually got the formula from Mike from a previous post :). I have two dates formatted like this - 4/9/2015 1:52:00 PMthis is the formula i ended up using :=INT(H3/24)&" Day(s) "&MOD(H3,24)&" Hours"That returns :0 Day(s) 23 HoursMy problem is that i have a large spreadsheet and I want to find the average turnaround time for all those dates. I was able to accomplish this by manually taking the calculator app and multiplying each day by 24 and adding the hours so that i can get a field for each row with the hours. Then i used =INT(E23/24)&" Day(s) "&MOD(E23,24)&" Hours" to get me the turnaroud time formatted the way i want. That worked for what i wanted but it was very time consuming adding up the hours. Is there a faster way to get the number of hours in a day from monday to friday from taking two dates?

Report •

Related Solutions

#4
April 13, 2015 at 14:11:06
 Why are you using a calculator app when you have Excel right in front of you?Drop all the text from your formulas, multiply the "days" portion by 24, add the hours and you're done.

Report •

#5
April 13, 2015 at 18:56:07