CODEWITHSUNDEEP
excelexcel-formula
Bihaqi Al Rafik
Bihaqi Al Rafik

Reputation: 39

How to get average time in excel

enter image description here

Hello everyone I have one question, I have one columns duration formatted time and I want to calculate the average time from the duration column, what is the excel formula?

Upvotes: 0

Views: 137

Answers (1)

JB-007
JB-007

Reputation: 2441

Screenshot/here refer:

This depends entirely upon how the values in your screenshot have been formatted - e.g. if they are value-based (col B, below), then average directly should do the trick (you could format this in similar way shown):

Value-based

i.e. you could use the following function per cell A7:

=AVERAGE(A2:A6)

However, if they are string based, then some string-manipulation is required - cell C7 illustrates:

=AVERAGE(MID(C2:C6,1,SEARCH(" ",C2:C6))*1+1*MID(C2:C6,SEARCH(",",C2:C6)+2,SEARCH("hours",C2:C6)-10)/24+SUBSTITUTE(MID(C2:C6,SEARCH("hours",C2:C6)+6,LEN(C2:C6))," mins","")/24/60)

Note: cells A7 & C7 in above are themselves formatted using custom format as follows:

"avg: "#,##0.00

Format for average (result cells: A8 & C8)

Applying the same format depicted for col A values returns the following:

Like-formatted output cell/duration

Upvotes: 2

Related Questions