How can I compute total time of an excel sheet?

robi10101298

New Member
Joined
Dec 15, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.12318:03:34.123
18:03:34​
18:03:34​
Feb 15 06:02:14.44406:02:14.444
06:02:14​
06:02:14​
Feb 15 06:02:15.70606:02:15.706
06:02:15​
06:02:15​
Feb 15 06:02:17.14306:02:17.143
06:02:17​
06:02:17​
Feb 15 06:02:18.69106:02:18.691
06:02:18​
06:02:18​
Feb 15 06:02:20.02006:02:20.020
06:02:20​
06:02:20​
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Format the cell giving you the result of the sum to [h]:mm:ss

the [h] part allows hours to go past 23
 
Upvote 0
Format the cell giving you the result of the sum to [h]:mm:ss
The cells are already formatted as [h]:mm:ss:
1671102028590.png



The sum it's giving me some random numbers:
1671102114722.png

if I modify from D20298 to D20297 I'll get a different number which it's not OK... :(
 
Upvote 0
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
Excel Formula:
=TIME(HOUR(A1),MINUTE(A1),SECOND(A1))
 
Upvote 0
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
Excel Formula:
=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 ...
1671103812800.png
 
Upvote 0
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
AB
1Feb 08 18:03:34.12318:03:34
2Feb 15 06:02:14.44406:02:14
3Feb 15 06:02:15.70606:02:16
4Feb 15 06:02:17.14306:02:17
5Feb 15 06:02:18.69106:02:19
6Feb 15 06:02:20.02006:02:20
7
848:15:00
Sheet1
Cell Formulas
RangeFormula
B1:B6B1=TIME(HOUR(A1),MINUTE(A1),SECOND(A1))
B8B8=SUM(B1:B6)
 
Upvote 0
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
AB
1Feb 08 18:03:34.12318:03:34
2Feb 15 06:02:14.44406:02:14
3Feb 15 06:02:15.70606:02:16
4Feb 15 06:02:17.14306:02:17
5Feb 15 06:02:18.69106:02:19
6Feb 15 06:02:20.02006:02:20
7
848:15:00
Sheet1
Cell Formulas
RangeFormula
B1:B6B1=TIME(HOUR(A1),MINUTE(A1),SECOND(A1))
B8B8=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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top