Use SUM to add time format

K

Kaykayme

I have 392 rows of accumulated hours to calculate. I have tried using the
sum function but it only gives 0. However, when I add individual cells I get
a total but it is not correct.
Example:
A1 = 37:10:46.54 and A2 = 23:11:00.00
Formated: [h]:mm:ss.00
Formula: =A1+A2.
I tried using a different time format [h]:mm:ss but I still get 0.

Is there a limit on rows to calculate?
 
J

John C

works fine for me. are we sure the values for A1 & A2 are formatted as time,
and not as text?
 
F

ForeverH

Hi kaykayme

i just tried your problem and got a correct answer. make sure the format in
the entire column (or at least the cell where you need the sum) has the same
time format

the sum i tried was in the format hh:mm:ss
 
S

Satti Charvak

You can use the below formula to calculate into seconds and do the sum total
and finally convert the data to hours, minutes and seconds

=HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1)
 
K

Kaykayme

Thank you all for your help. What seems to be the problem is the original
format was different for each row (Ex. 37:10:46:54, 23:11, 6:26:54) So I
formatted a column as text and using vba copied the text of each cell into
the text formatted column so that they all were uniform in format (I added
0's and colons as needed) then changed the format to [h]:mm:ss.00. However,
the numbers values show as text and not as numbers or elasped time. This
data was parsed from a spreadsheet derived from html. So when the elapsed
time includes milliseconds it shows as text. I need a formula to convert
the text to a number that would represent the elasped time including
milliseconds.

Satti Charvak said:
You can use the below formula to calculate into seconds and do the sum total
and finally convert the data to hours, minutes and seconds

=HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1)


--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India


Kaykayme said:
I have 392 rows of accumulated hours to calculate. I have tried using the
sum function but it only gives 0. However, when I add individual cells I get
a total but it is not correct.
Example:
A1 = 37:10:46.54 and A2 = 23:11:00.00
Formated: [h]:mm:ss.00
Formula: =A1+A2.
I tried using a different time format [h]:mm:ss but I still get 0.

Is there a limit on rows to calculate?
 
D

David Biddulph

37:10:46:54 is not a valid time format, so it would indeed by treated as
text. The seconds and decimals of seconds should be separated by a decimal
point, not by a colon.
37:10:46.540 is how it will look if you've got a real time and formatted it
to show the milliseconds.
--
David Biddulph

Kaykayme said:
Thank you all for your help. What seems to be the problem is the original
format was different for each row (Ex. 37:10:46:54, 23:11, 6:26:54) So I
formatted a column as text and using vba copied the text of each cell into
the text formatted column so that they all were uniform in format (I added
0's and colons as needed) then changed the format to [h]:mm:ss.00.
However,
the numbers values show as text and not as numbers or elasped time. This
data was parsed from a spreadsheet derived from html. So when the elapsed
time includes milliseconds it shows as text. I need a formula to convert
the text to a number that would represent the elasped time including
milliseconds.

Satti Charvak said:
You can use the below formula to calculate into seconds and do the sum
total
and finally convert the data to hours, minutes and seconds

=HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1)


--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India


Kaykayme said:
I have 392 rows of accumulated hours to calculate. I have tried using
the
sum function but it only gives 0. However, when I add individual cells
I get
a total but it is not correct.
Example:
A1 = 37:10:46.54 and A2 = 23:11:00.00
Formated: [h]:mm:ss.00
Formula: =A1+A2.
I tried using a different time format [h]:mm:ss but I still get 0.

Is there a limit on rows to calculate?
 
K

Kaykayme

Hello Everyone,
I finally got it to work! I wrote a function that counts the number of
colons in the time cell. On the cells that have time formats with one or two
colons, I just changed the format in the destination cell to [h]:mm:ss.00 and
copied the time value. But on the cells that had three colons changed the
format in the destination cell to [h]:mm:ss.00 and added a formula to the
source cell that substituted the third colon for a decimal. Then created a
variable for the text in the source cell and added a formula which changed
the text to a value in the destination cell. An example is below:

For Each c In Worksheets("User Data (2)").Range("H2:H393")
d = d + 1

strVTimeCell = Worksheets("User Data (2)").Range("F" & CStr(d))

Select Case FindColon(d)
Case 1
With Worksheets("User Data (2)").Range("J" & CStr(d))
.Value = strVTimeCell
.NumberFormat = "[h]:mm:ss.00"
End With
Case 2
With Worksheets("User Data (2)").Range("J" & CStr(d))
.Value = strVTimeCell
.NumberFormat = "[h]:mm:ss.00"
End With
Case 3

Worksheets("User Data (2)").Range("H" & CStr(d)).NumberFormat =
"[h]:mm:ss.00"
Worksheets("User Data (2)").Range("H" & CStr(d)).Formula =
"=SUBSTITUTE(F" & d & "," & _
"""" & ":" & """" & "," & """" & "." & """" & ",3)"
strTime = Worksheets("User Data (2)").Range("H" & CStr(d)).Value
With Worksheets("User Data (2)").Range("J" & CStr(d))
.NumberFormat = "[h]:mm:ss.00"
.Formula = "=VALUE(" & """" & strTime & """" & ")"
End With
End Select
Next c


Kaykayme said:
Thank you all for your help. What seems to be the problem is the original
format was different for each row (Ex. 37:10:46:54, 23:11, 6:26:54) So I
formatted a column as text and using vba copied the text of each cell into
the text formatted column so that they all were uniform in format (I added
0's and colons as needed) then changed the format to [h]:mm:ss.00. However,
the numbers values show as text and not as numbers or elasped time. This
data was parsed from a spreadsheet derived from html. So when the elapsed
time includes milliseconds it shows as text. I need a formula to convert
the text to a number that would represent the elasped time including
milliseconds.

Satti Charvak said:
You can use the below formula to calculate into seconds and do the sum total
and finally convert the data to hours, minutes and seconds

=HOUR(A1)*3600+MINUTE(A1)*60+SECOND(A1)


--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India


Kaykayme said:
I have 392 rows of accumulated hours to calculate. I have tried using the
sum function but it only gives 0. However, when I add individual cells I get
a total but it is not correct.
Example:
A1 = 37:10:46.54 and A2 = 23:11:00.00
Formated: [h]:mm:ss.00
Formula: =A1+A2.
I tried using a different time format [h]:mm:ss but I still get 0.

Is there a limit on rows to calculate?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top