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?

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


--
** John C **

"Kaykayme" wrote:

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?

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

"Kaykayme" wrote:

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?

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" wrote:

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?

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" wrote:

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" wrote:

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?

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" wrote in message
...
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" wrote:

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" wrote:

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?

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" wrote:

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" wrote:

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" wrote:

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?