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

[FanofExcel18]

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]

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

2.

	A​	B​	C​	D​
1​	​	​	​	​
2​	Input:​	9:08:50​	B2: Input
3​	To secs:​	32930​	B3: =B2*86400
4​	Back to time:​	9:08:50​	B4: =B3/86400

 

[brawnystaff]

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:

=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:

=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):

=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:

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]

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]

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

I need it to basically take these numbers:

3:19:55

9:08:50

9:19:02

8:48:36

8:00:07

8:29:23

8:24:49

:12:36 and the Sum function should equal : 55:43:18 <tbody> </tbody>

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

 

[shg]

	A​	B​
1​	3:19:55​
2​	9:08:50​
3​	9:19:02​
4​	8:48:36​
5​	8:00:07​
6​	8:29:23​
7​	8:24:49​
8​	0:12:36​
9​	55:43:18​	A9: =SUM(A1:A8)
10​		Format of A9: [h]:mm:ss

 

[FanofExcel18]

The custom cell worked; thank you to all for your help!