Trying to use a duration format as numbers in Excel 2013.

[Sjenkins]

I have a report that I ran that gave me a duration in hours:minutes:seconds and I'm trying to take the average of them all. I was able to get it to work in Google spreadsheets by setting the custom to hh.mm.ss. This is it working in Google Sheets:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

23:49:33
23:21:00
22:33:05
22:14:52
22:09:30
21:53:50			16:29:06
20:20:51
19:43:42
19:29:02
19:20:17
19:14:32
18:17:40
18:13:59
17:55:18
17:15:55
17:04:25
17:03:58
16:45:57
16:18:41
15:54:57
15:17:42
15:00:18
13:18:24
13:17:01
12:55:30
12:50:52
11:56:44
11:52:59
11:15:57
11:00:02
10:54:07
10:43:14
10:34:44
10:30:59

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>And this is it in Excel 2013:

23:49:33
23:21:00
22:33:05
22:14:52
22:09:30
21:53:50
20:20:51
19:43:42
19:29:02	#DIV/0!
19:20:17
19:14:32
18:17:40
18:13:59
17:55:18
17:15:55
17:04:25
17:03:58
16:45:57
16:18:41
15:54:57
15:17:42
15:00:18
13:18:24
13:17:01
12:55:30
12:50:52
11:56:44
11:52:59
11:15:57
11:00:02
10:54:07
10:43:14
10:34:44
10:30:59

<colgroup><col><col></colgroup><tbody>
</tbody>

Both times I'm taking the average of the duration times by going =AVERAGE(A1:A35). Any idea how I can get it to work in Excel? The client won't be able to accept a Google sheet format and it breaks if I try to paste it in.

 

[PaddyD]

Hi - welcome to the board.

In the excel file, your times are being held as text values, not numbers, hence the error message. are a number of ways to fix, depending on exactly what's causing the data type confusion (e.g. select & copy a blank cell, select range of 'numbers', and paste special | add).