Averaging a column for average turn around time. Column has # of days, hours, minutes

%3CLINGO-SUB%20id%3D%22lingo-sub-2149320%22%20slang%3D%22en-US%22%3EAveraging%20a%20column%20for%20average%20turn%20around%20time.%20Column%20has%20%23%20of%20days%2C%20hours%2C%20minutes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2149320%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20average%20column%20S%20in%20this%20spreadsheet.%26nbsp%3B%20%26nbsp%3BThis%20is%20the%20%23of%20day%2C%20hours%20and%20minutes%20it%20took%20an%20analyst%20to%20complete%20a%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20average%20this%20in%20Column%20S.%26nbsp%3B%20%26nbsp%3B%20I%20think%20my%20issue%20is%20it%20thinks%20it%20is%20text%20fields%2C%20but%20I%20do%20not%20know%20how%20to%20convert%20to%20be%20able%20to%20get%20an%20average%20turn%20around%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2149320%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2149467%22%20slang%3D%22en-US%22%3ERe%3A%20Averaging%20a%20column%20for%20average%20turn%20around%20time.%20Column%20has%20%23%20of%20days%2C%20hours%2C%20minutes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2149467%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F972314%22%20target%3D%22_blank%22%3E%40tmatthews2172%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%20the%20following%20formula%20in%20S13%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DNETWORKDAYS(N13%2CO13%2C%24W%2414%3A%24W%2421)-1%2BMOD(O13%2C1)-MOD(N13%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EApply%20the%20custom%20number%20format%20d%20hh%3Amm%20or%20d%5C%3Ahh%3Amm.%3C%2FP%3E%0A%3CP%3EThe%20average%20will%20work%20correctly.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPS%20This%20formula%20returns%20different%20results%20than%20you%20have%2C%20for%20several%20reasons.%20I%20think%20mine%20is%20better%2C%20but%20please%20check%20carefully.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am trying to average column S in this spreadsheet.   This is the #of day, hours and minutes it took an analyst to complete a file.

 

I would like to average this in Column S.    I think my issue is it thinks it is text fields, but I do not know how to convert to be able to get an average turn around time.

3 Replies

@tmatthews2172 

Use the following formula in S13:

=NETWORKDAYS(N13,O13,$W$14:$W$21)-1+MOD(O13,1)-MOD(N13,1)

Apply the custom number format d hh:mm or d\:hh:mm.

The average will work correctly.

 

PS This formula returns different results than you have, for several reasons. I think mine is better, but please check carefully.

@Hans Vogelaar    Thank you so much.   I am not an advanced excel user.   

 

I ran it thank you, I agree, this is a better measure.

 

For purpose of understanding this formula better, what does this portion specifically tell excel to do

 

)-1+MOD(O13,1)-MOD(N13,1)

@tmatthews2172 

MOD(O13, 1) takes the fractional part of the date/time in O13; it removes the date and returns only the time.

The same for MOD(N13, 1).

MOD(O13,1)-MOD(N13,1) subtracts the times.