CODEWITHSUNDEEP
mysqlexceltimeexcel-formula
Brainsong
Brainsong

Reputation: 11

Format Excel, column to get sum of hh:mm:ss when they are listed that way

I need to get the total duration time in hours:minutes:seconds when the list of time is all in one column. The seconds need to convert into minutes and seconds when they exceed 60 and the minutes into minutes and hours. I am getting the original data from a CVS format so it must all go into one column. I need to use a formula that will give me the sum for each person's individual times.

For example: a column like 01:22:03 added to 00:58:57 must come out to 02:21:00. I have tried putting the format into custom time h:mm:ss but it does not work. I have far too many to add on a weekly basis to do it by hand and I know someone has an answer for this new Excel user.

Upvotes: 1

Views: 18321

Answers (6)

Ramek Nitram
Ramek Nitram

Reputation: 1

Use the sum product function. I am no wiz I just googled the crap out of it. What happens is that the duration is converted into text so you have to convert the text to value. So for eg =sumproduct((a2:a50)*1) then to get the duration you have to format the cells to duration and it'll get the number. This is the site that helped me to figure it out

https://a4accounting.com.au/how-to-sum-text-numbers-in-excel/

Upvotes: 0

manuel
manuel

Reputation: 1840

Since I'm programmer, I use VBA for everything :) here is little VBA (Macro) for that. There isn't test for input format so code will break if column do not contains fields like 01:12:13

'' SUM time in Column
'' Choose Column,StartRow,EndRow
'' result will be placed at Column:EndRow + 1
'' Column=1  1=A,2=B, ...

Dim w As Worksheet
Set w = ActiveWorkbook.Worksheets(1) ''choose first worksheet

Dim d As Double
Dim Column, StartRow, EndRow As Integer

Column = 1 ''this is A Column
StartRow = 1
EndRow = 21

For i = StartRow To EndRow
    d = d + CDate(w.Cells(i, Column)) * 24 * 60 * 60
Next

''convert seconds to days

d = d / 86400.0
''display result in EndRow+1
w.Cells(EndRow + 1, Column) = Format(d, "hh:mm:ss")

Upvotes: 0

Fernando
Fernando

Reputation: 1

If your format is always with this structure (00:00:00) you can use:

01:22:03(A1) 00:58:57(B1) 0 (C1) 2 (D1) 21(E1) 0 (F1) 02:21:00(G1)

05:22:04(A2) 00:50:40(B2) 0 (C2) 6 (D2) 12(E2) 44(F2) 06:12:44(G2)

11:22:05(A3) 00:08:59(B3) 0 (C3) 11(D3) 31(E3) 4(F3) 11:31:04(G3)

D = horas; E = minutes; F = Seconds

Formulas used: C1 =(NSHORA(EXTRAE(A2;1;2);EXTRAE(A2;4;2);EXTRAE(A2;7;2)))+NSHORA(EXTRAE(B2;1;2);EXTRAE(B2;4;2);EXTRAE(B2;7;2))

d1 =HORA(C1)

e1 =MINUTO(C1)

f1 =SEGUNDO(C1)

We could finally get in a different column the result with your format if necessary with this one:

=CONCATENAR(SI(LARGO(D2)<2;CONCATENAR(0;D2);D2);":";SI(LARGO(E2)<2;CONCATENAR(0;E2);E2);":";SI(LARGO(F2)<2;CONCATENAR(0;F2);F2))

Select range C1:G1 and copy it to get what you want. After it you could also hide columns c, d, e and f

Upvotes: 0

YankTHEcode
YankTHEcode

Reputation: 652

Add all the cells with AutoSum.

All you need do is apply an appropriate format to its result:

Select the cell containing the total

From the Format menu, choose the Cells command

From the Category list, select Custom

In the Type box, input the format [h]:mm (note the use of square brackets).

If you want to show seconds in your total, input [h]:mm:ss (this format is listed in Excel's Custom formats)

Click OK.

all the best

Upvotes: 2

gtwebb
gtwebb

Reputation: 3011

If they are all stored as actual times, you should just be able to use a custom format of [hh]:mm:ss (this allows the hour total to go above 24.

If this isn't working then it is likely they are likely stored as text in which case you will need to split them and recombine them either through formulas or using text to columns.

If it is stored as a proper date =isnumber(your cell) should return TRUE, if not it is text.

If you are copying and pasting the data in make sure the cells aren't formatted as text before you do as it could cause issues.

Upvotes: 1

BRM
BRM

Reputation: 201

use the timevalue() function to convert a string of hh:mm:ss into a serial number that can be summed and displayed as a hh:mm:ss number.

Upvotes: 3

Related Questions