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

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

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

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.

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

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

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

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.