How can I compute total time of an excel sheet?

[robi10101298]

I need to compute the total time in which a system was down, and it looks like using the SUM(C1:C20298), it's not working for me, it's giving me some results that are not accurate.. The problem is that I have random timestamps like 17:27:22.432, then 08:04:25.532, and those are over 24h, so it's a little bit more complicated to compute..

Feb 08 18:03:34.123	18:03:34.123	18:03:34​			18:03:34​
Feb 15 06:02:14.444	06:02:14.444	06:02:14​			06:02:14​
Feb 15 06:02:15.706	06:02:15.706	06:02:15​			06:02:15​
Feb 15 06:02:17.143	06:02:17.143	06:02:17​			06:02:17​
Feb 15 06:02:18.691	06:02:18.691	06:02:18​			06:02:18​
Feb 15 06:02:20.020	06:02:20.020	06:02:20​			06:02:20​

 

[Georgiboy]

Format the cell giving you the result of the sum to [h]:mm:ss

the [h] part allows hours to go past 23

 

[robi10101298]

Format the cell giving you the result of the sum to [h]:mm:ss

The cells are already formatted as [h]:mm:ss:

The sum it's giving me some random numbers:

if I modify from D20298 to D20297 I'll get a different number which it's not OK...

 

[Georgiboy]

I can't see the formulae you have used to create column C but give the below a go to create a valid time in column C and then sum thatL

=TIME(HOUR(A1),MINUTE(A1),SECOND(A1))

 

[robi10101298]

I can't see the formulae you have used to create column C but give the below a go to create a valid time in column C and then sum thatL

=TIME(HOUR(A1),MINUTE(A1),SECOND(A1))

On column C it's not a formula, it's a text transformed to time.
If I use your formula I get the time in 12h format, but if I use SUM(c1,c2), I basically get c1+c2 and it;s not OK, if I have 17:15+17:15, it'll give to me: 34:30, not the total time ...

 

[Georgiboy]

I know the formula emits a 12 hour format but if you add those cells together with sum it gives the total amount of hours for the summed values. I thought this was the goal?

The below was in my mind:

Book1
	A	B
1	Feb 08 18:03:34.123	18:03:34
2	Feb 15 06:02:14.444	06:02:14
3	Feb 15 06:02:15.706	06:02:16
4	Feb 15 06:02:17.143	06:02:17
5	Feb 15 06:02:18.691	06:02:19
6	Feb 15 06:02:20.020	06:02:20
7
8		48:15:00
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=TIME(HOUR(A1),MINUTE(A1),SECOND(A1))
B8	B8	=SUM(B1:B6)

 

[robi10101298]

I know the formula emits a 12 hour format but if you add those cells together with sum it gives the total amount of hours for the summed values. I thought this was the goal?

The below was in my mind:

Book1
	A	B
1	Feb 08 18:03:34.123	18:03:34
2	Feb 15 06:02:14.444	06:02:14
3	Feb 15 06:02:15.706	06:02:16
4	Feb 15 06:02:17.143	06:02:17
5	Feb 15 06:02:18.691	06:02:19
6	Feb 15 06:02:20.020	06:02:20
7
8		48:15:00
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=TIME(HOUR(A1),MINUTE(A1),SECOND(A1))
B8	B8	=SUM(B1:B6)

Not exactly, for example, if I have

18:03:31​

18:03:32​

18:03:33​

18:03:33​

18:03:34​

06:02:14​

06:02:15​

06:02:17​

06:02:18​

The total should be: 00:05:09

 

[Georgiboy]

I think i have got lost here, I think i may have missed some detail as I am not sure how those most recent values add up to 00:05:09

 

[robi10101298]

I think i have got lost here, I think i may have missed some detail as I am not sure how those most recent values add up to 00:05:09

Basically, what I want it's to compute the total time in minutes for multiple timestamps, not just to add multiple hours and get a big number and that's it, this is what I want...
How I computed: well, if I have

18:03:31 18:03:32 18:03:33 18:03:33 18:03:34

then the total should be 0 hours, 0 minutes, and 4 seconds.
and if I have

18:03:31

18:03:32

18:03:33

18:03:33

18:03:34 18:04:34

06:02:14

06:02:15

then the total would be:
0 hours, 1 minutes, and 6 seconds

 

[jdellasala]

Time is not so easy in Excel. Remember, FORMATTING a number does NOT change the underlying value. That's why if you only want 2 decimal places to be displayed, it's best to use ROUND to get there rather than just formatting to two decimal places which can lead to incorrect answers.

That said, Time is a numeric value between 0 (12:00:00 AM or 0:0.0) and 0.99999 (11:59:59 PM or 59:59:0) depending on the time format (time or duration).
Just to complicate matters, if you pass through Midnight, the duration has to compensate for that. It's a bit more than should be addressed at this level, but you should watch this excellent video How to Calculate Hours Worked with Excel Power Query (& Properly Sum time) before diving deeper into the initial question.

Less than 10 minutes, well worth it, and a sample file available.