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)?

✔ 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:46and 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)))/1440So 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)))/1440So it returns 16.71944444

I myself would probably opt for the last formula.

MIKE

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

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:46Your (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.

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?

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.

DerbyDad03, Curious about your opinion on the NETWORKDAYS() function. Using the dates:

A3: 1/31/2015 2:30

B3: 1/7/2015 19:46and 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)))/1440So 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)))/1440So it returns 16.71944444

I myself would probably opt for the last formula.

MIKE

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

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History