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

Report •


✔ Best Answer
April 13, 2015 at 18:56:07
DerbyDad03, Curious about your opinion on the NETWORKDAYS() function.

Using the dates:

A3: 1/31/2015 2:30
B3: 1/7/2015 19:46

and your formula

=(NETWORKDAYS(B3,A3)*1440+HOUR(MOD(B3-A3,1))*60+MINUTE(MOD(B3-A3,1)))/1440

We get 18.71944444

But, since NETWORKDAYS() counts both the Start Date & End Date
and 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)))/1440

So it returns 17.71944444

There 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)))/1440

So it returns 16.71944444

I myself would probably opt for the last formula.

MIKE

http://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.

MIKE

http://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)))/1440

e.g.:

A3: 1/31/2015 2:30
B3: 1/7/2015 19:46

Your (corrected) formula returns:

18 Days 17 Hour 16 Minutes

My formula returns:

18.71944444

You can do all sorts of calculations on 18.71944444

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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 PM

this is the formula i ended up using :

=INT(H3/24)&" Day(s) "&MOD(H3,24)&" Hours"

That returns :0 Day(s) 23 Hours

My 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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
April 13, 2015 at 18:56:07
✔ Best Answer
DerbyDad03, Curious about your opinion on the NETWORKDAYS() function.

Using the dates:

A3: 1/31/2015 2:30
B3: 1/7/2015 19:46

and your formula

=(NETWORKDAYS(B3,A3)*1440+HOUR(MOD(B3-A3,1))*60+MINUTE(MOD(B3-A3,1)))/1440

We get 18.71944444

But, since NETWORKDAYS() counts both the Start Date & End Date
and 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)))/1440

So it returns 17.71944444

There 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)))/1440

So it returns 16.71944444

I myself would probably opt for the last formula.

MIKE

http://www.skeptic.com/


Report •

#6
April 14, 2015 at 03:54:15
You are probably right. I didn't actually check the results, I simplied copied the formula from Jay2kidd's post. At first it didn't work at all because the construction of the NETWORKDAYS function was incorrect.

Once that was fixed, I assumed he was happy with the results since all he asked about was the fact that he couldn't use mathematical functions on the result because of the text portions. I simply rewrote so that it returned a number not a "sentence". Had he mentioned that he wasn't getting the correct results, I would have dug a illite further.

Good catch!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •


Ask Question