Finding an Average for times

Ill display below but i figured ill let you know the issue first.

I have a list of wait times. The time are in the form of either : 20m ; 20min ; 1hr; 15-20 min;15-20 m.

I want to find and average wait time for different people. The question, i believe is, how can i get rid of the text automatically behind the numbers and also remove the hyphen between some numbers and find thier average, then to be able to just find the average per user. The last part is, seemingly, the easiest part as in i have that for certain. However, i need to get excel to recognize the numbers to be able to find averages to begin with.

ie:
time person
20min 1
30min 4
20m 4
15-20min 2
1 hr 3




Hope i am clear enough. Get average times without all the extra characters in the way. I would prefer to use some repeatable and can populate because i am trying to build out a template and have currently almost run to an end. I am not shy in using VBA (have tried a little) but i removed the macros.

Thanks ahead of time

This will deal with making the different time units compatible (and convert to Excel date/time):
EvaluateTime.jpg

Add this vba function to a code module, and enter the formula as shown above into cells C2:C7. Formula in G2 is '=AVERAGEIF($A$2:$A$7,$F2,$C$2:$C$7)', copy to G2:G5.

Please Login or Register  to view this content.