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>

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
[TD="align: right"]23:49:33[/TD]

[TD="align: right"]23:21:00[/TD]

[TD="align: right"]22:33:05[/TD]

[TD="align: right"]22:14:52[/TD]

[TD="align: right"]22:09:30[/TD]

[TD="align: right"]21:53:50[/TD]

[TD="align: right"]16:29:06[/TD]

[TD="align: right"]20:20:51[/TD]

[TD="align: right"]19:43:42[/TD]

[TD="align: right"]19:29:02[/TD]

[TD="align: right"]19:20:17[/TD]

[TD="align: right"]19:14:32[/TD]

[TD="align: right"]18:17:40[/TD]

[TD="align: right"]18:13:59[/TD]

[TD="align: right"]17:55:18[/TD]

[TD="align: right"]17:15:55[/TD]

[TD="align: right"]17:04:25[/TD]

[TD="align: right"]17:03:58[/TD]

[TD="align: right"]16:45:57[/TD]

[TD="align: right"]16:18:41[/TD]

[TD="align: right"]15:54:57[/TD]

[TD="align: right"]15:17:42[/TD]

[TD="align: right"]15:00:18[/TD]

[TD="align: right"]13:18:24[/TD]

[TD="align: right"]13:17:01[/TD]

[TD="align: right"]12:55:30[/TD]

[TD="align: right"]12:50:52[/TD]

[TD="align: right"]11:56:44[/TD]

[TD="align: right"]11:52:59[/TD]

[TD="align: right"]11:15:57[/TD]

[TD="align: right"]11:00:02[/TD]

[TD="align: right"]10:54:07[/TD]

[TD="align: right"]10:43:14[/TD]

[TD="align: right"]10:34:44[/TD]

[TD="align: right"]10:30:59[/TD]

</tbody>

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

[TABLE="width: 357"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]23:49:33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23:21:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22:33:05[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22:14:52[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22:09:30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21:53:50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20:20:51[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19:43:42[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19:29:02[/TD]
[TD="align: center"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD]19:20:17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19:14:32[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18:17:40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18:13:59
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17:55:18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17:15:55[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17:04:25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17:03:58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16:45:57[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16:18:41[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15:54:57[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15:17:42[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15:00:18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13:18:24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13:17:01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12:55:30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12:50:52[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11:56:44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11:52:59[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11:15:57[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11:00:02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:54:07[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:43:14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:34:44[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10:30:59[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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