Suming or averaging time column

[HomePro]

So I have a program that gives me a CSV that I can not seem to get totals or averages on the column marked duration.
Any idea what is happening?

zoomus_call_log_2022-07-19.csv
	G	H	I	J
1	Time	Result	Path	Duration (hh:mm:ss)
2	7/18/2022 16:20	Call Connected	Extension	0:02:37
3	7/18/2022 15:51	Call Connected	Extension	0:03:30
4	7/18/2022 12:56	Call Connected	Extension	0:00:25
5	7/18/2022 12:41	Call Connected	Extension	0:02:40
6	7/18/2022 11:16	Call Connected	Extension	0:01:43
7	7/18/2022 10:23	Call Connected	Extension	0:00:41
8	7/18/2022 10:18	Call Connected	Extension	0:02:15
9	7/18/2022 10:03	Call Connected	Extension	0:07:27
10	7/18/2022 9:28	Call Connected	Extension	0:04:26
11	7/18/2022 9:19	Call Connected	Extension	0:16:46
mia

 

[etaf]

comes across as a time value - which is a decimal value , below 1 and then TIME formats to time display

what is it you are trying to average or total ?

you may need a format [H]:mm - if time goes over 24hrs

it maybe its comming into your file as text

change the format to general and see if you get a decimal number

Book2
	A	B	C	D	E	F
1	Time	Result	Path	Duration (hh:mm:ss)
2	7/18/22 16:20	Call Connected	Extension	00:02:37
3	7/18/22 15:51	Call Connected	Extension	00:03:30
4	7/18/22 12:56	Call Connected	Extension	00:00:25
5	7/18/22 12:41	Call Connected	Extension	00:02:40
6	7/18/22 11:16	Call Connected	Extension	00:01:43
7	7/18/22 10:23	Call Connected	Extension	00:00:41
8	7/18/22 10:18	Call Connected	Extension	00:02:15
9	7/18/22 10:03	Call Connected	Extension	00:07:27
10	7/18/22 9:28	Call Connected	Extension	00:04:26	SUM	Average
11	7/18/22 9:19	Call Connected	Extension	00:16:46	00:42:30	00:04:15
Sheet1

Cell Formulas
Range	Formula
E11	E11	=SUM(D2:D11)
F11	F11	=AVERAGE(D2:D11)

 

[alansidman]

An alternative means is Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Time"}, {{"Sum", each List.Sum([#"Duration (hh:mm:ss)"]), type number}, {"Average", each List.Average([#"Duration (hh:mm:ss)"]), type number}})
in
    #"Grouped Rows"