Sum time durations of over 100 hours, formatted as hhh:mm:ss

rjone49 · Aug 20, 2017

I have a list of time duration in functions that I need to SUM.
They are formatted as hhh:mm:ss, coming from another source. I can export to Excel, but once I put the hundred hours, it formats as text and won't allow calculation.
How do I force it back to a numeric format?
[TABLE="width: 86"]
<colgroup><col></colgroup><tbody>[TR]
[TD]011:25:29[/TD]
[/TR]
[TR]
[TD]005:47:32[/TD]
[/TR]
[TR]
[TD]006:58:34[/TD]
[/TR]
[TR]
[TD]017:56:45[/TD]
[/TR]
[TR]
[TD]011:53:13[/TD]
[/TR]
[TR]
[TD]019:32:15[/TD]
[/TR]
[TR]
[TD]023:16:48[/TD]
[/TR]
[TR]
[TD]007:40:42[/TD]
[/TR]
[TR]
[TD]009:21:22[/TD]
[/TR]
[TR]
[TD]009:26:29[/TD]
[/TR]
[TR]
[TD]018:57:59[/TD]
[/TR]
[TR]
[TD]006:41:59[/TD]
[/TR]
</tbody>[/TABLE]

FDibbins · Aug 20, 2017

I have a feeling that all of your data is text, especially if it is coming from an outside source. Test a few that you thing are time, with =isnumber(cell-ref)...FALSWE indicates text. Another thing that indicates that, is that unless you have applied some fancy formatting, the time would never show with a leading 0.

Do the test I suggested and get back to me?

Tetra201 · Aug 20, 2017

Maybe this:

=--REPLACE(A1,1,LEFT(A1)="0","")

rjone49 · Aug 21, 2017

Yes, it is text.
Tetra201's formula removed the leading zero, and gave me a decimal date that I can reformat and work.
If I manually replace the 0 with 1, it reformatted the original cell as a date I can sum, but not edit (well I can but not easily).
I'm not sure how it will import with hundreds of hours.
Thanks for your involvement

FDibbins · Aug 21, 2017

This will do the conversion for you, but it displays the answer in decimal, not time - seems excel does not like to deal with 100+ hours.

Time is just a decimal of 1 (day) anyway, so you can still run calcs on this and they should work out right
[Table="width:, class:grid"][tr][td] [/td][td]

A

[/td][td]

B

[/td][/tr]
[tr][td]

1

[/td][td]011:25:29[/td][td]

11.0177

[/td][/tr]

[tr][td]

2

[/td][td]005:47:32[/td][td]

5.0330

[/td][/tr]

[tr][td]

3

[/td][td]006:58:34[/td][td]

6.0407

[/td][/tr]

[tr][td]

4

[/td][td]017:56:45[/td][td]

17.0394

[/td][/tr]

[tr][td]

5

[/td][td]011:53:13[/td][td]

11.0370

[/td][/tr]

[tr][td]

6

[/td][td]019:32:15[/td][td]

19.0224

[/td][/tr]

[tr][td]

7

[/td][td]023:16:48[/td][td]

23.0117

[/td][/tr]

[tr][td]

8

[/td][td]007:40:42[/td][td]

7.0283

[/td][/tr]

[tr][td]

9

[/td][td]099:21:22[/td][td]

99.0148

[/td][/tr]

[tr][td]

10

[/td][td]119:26:29[/td][td]

119.0184

[/td][/tr]

[tr][td]

11

[/td][td]018:57:59[/td][td]

18.0403

[/td][/tr]

[tr][td]

12

[/td][td]006:41:59[/td][td]

6.0292

[/td][/tr]
[/table]
B1=LEFT(A1,3)+MID(A1,5,2)/1440+RIGHT(A1,2)/86400
copied down

Tetra201 · Aug 21, 2017

Just to mention:

Excel-2010 and 2013 accept entries up to 9999:59:59 with no leading zeros (exception: 0x:xx:xx).

FDibbins · Aug 21, 2017

Tetra, I am on 2013 (here at work), and while, yes, it will accept times > 100, I am having a hard time formatting these from that formula. If you enter (manually) times > 100, excel custom formats to [h]:mm:ss, but if you apply that same formay to my formula, it does not show correctly

[Table="width:, class:grid"][tr][td] [/td][td]

A

[/td][td]

B

[/td][td]

C

[/td][/tr]
[tr][td]

1

[/td][td][/td][td]Num/Gen[/td][td][h]:mm:ss[/td][/tr]

[tr][td]

2

[/td][td]011:25:29[/td][td]

11.0177

[/td][td]