Logged hh:mm:ss reading as a timestamp in Excel

FanofExcel18 · Jul 12, 2018

2 questions centering around the same report, 2 different ways of getting it and 2 different problems:

1.
Report generating from database has total logged time by hh:mm:ss.

When I attempt to sum up the time, about 10 cells, each cell reads as a timestamp not as hh:mm:ss.
Example: 9:08:50 = 9 hours, 8 Minutes, 50 Seconds
Reading as: 9:08:50 AM

How can I make it read as hh:mm:ss and sum it up?

2.
Same report can be generated by seconds. I did a calculation by taking the time then dividing by 60(convert to Minutes), divide again at 60 (convert to hours). There is a discrepancy by a few minutes in my calculation compared to the 1st way to run the report. Could anyone provide what I am doing wrong?
Example: 9:08:50 from the 1st report
Reading from 2nd report based on my calculation: 9:15

Much appreciated for any help!

shg · Jul 12, 2018

1. Just format the result as h:mm:ss

2.
[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A

[/td][td="bgcolor:#C0C0C0"]

B

[/td][td="bgcolor:#C0C0C0"]

C

[/td][td="bgcolor:#C0C0C0"]

D

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1

[/td][td="bgcolor:#F3F3F3"]

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2

[/td][td]

Input:

[/td][td]

9:08:50

[/td][td]B2: Input[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3

[/td][td]

To secs:

[/td][td]

32930

[/td][td]B3: =B2*86400[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4

[/td][td]

Back to time:

[/td][td]

9:08:50

[/td][td]B4: =B3/86400[/td][td][/td][/tr]
[/table]

brawnystaff · Jul 12, 2018

Regarding your issue with #1 , convert time to seconds, sum, then convert sum to h:mm:ss. If A1 has your value, formula would be as follows:

Code:

=A1*24*60*60

Thus, 00:01:41 would become 101 (101 seconds). Copy formula down to convert other values to seconds. Then, highlight all values converted to seconds and autosum (formula would be as follows:

Code:

=SUM(B1:B5)

Once you have your summed seconds, convert summed seconds to h:mm:ss using following formula (B6 assumed to be cell with total):

Code:

=B6/86400

Should give you a decimal number. Right-click on that cell, change Format to h:mm:ss (under Custom).

A VBA solution would be as follows:

Code:

SumTime()
'Add up HH:MM:SS from selection
Dim d As Double
Dim Cell As Range, Rng As Range
Set Rng = Selection


For Each Cell In Rng


    d = d + CDate(Cell.Value) * 24 * 60 * 60
Next Cell


''convert seconds to days


d = d / 86400#
''display result in Msgbox
MsgBox Format(d, "hh:mm:ss")
End Sub

JoeMo · Jul 12, 2018

If the sum is less than 1 day, "h:mm:ss" will do it. If greater than one day you will need "[h]:mm:ss"

The latter format works for less than one day as well.

FanofExcel18 · Jul 12, 2018

I have attempted everyone's suggestions but it hasn't worked.

I need it to basically take these numbers:
[TABLE="width: 86"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]3:19:55[/TD]
[/TR]
[TR]
[TD="align: right"]9:08:50[/TD]
[/TR]
[TR]
[TD="align: right"]9:19:02[/TD]
[/TR]
[TR]
[TD="align: right"]8:48:36[/TD]
[/TR]
[TR]
[TD="align: right"]8:00:07[/TD]
[/TR]
[TR]
[TD="align: right"]8:29:23[/TD]
[/TR]
[TR]
[TD="align: right"]8:24:49[/TD]
[/TR]
[TR]
[TD]:12:36

and the Sum function should equal : [TABLE="width: 86"]
<tbody>[TR]
[TD="class: xl65, width: 86, align: right"]55:43:18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

shg · Jul 12, 2018

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

A

[/td][td="bgcolor:#C0C0C0"]

B

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1

[/td][td]

3:19:55