removing digit or calc LEN fields

T

tri_p

I have had help here stripping a digit off a field with a LEN funciton.
Which is going from HHH:MM:SS to HH:MM:SS. With the LEN statment I insert a
column, the new column holds the LEN statement and coverts correctly however
now I can't sum the times in the group (I'm assuming its because all the
columns are calculated LEN statements. How can I either add the column OR
create a macro to remove the furtherest 0 (which would be HHH to HH)?

thanks
 
J

JE McGimpsey

If I understand you, you want to strip off any hours > 100. If that's
the case, one way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(MOD(G1:G10,100/24))
 
T

tri_p

sorry I wasn't very clear here is what I have:

column c
000:02:25
000:08:40

I can't get excel to sum that. If I run this in the d column
=IF(LEN(C2)<=8,C2,RIGHT(C2,LEN(C2)-(LEN(C2)-8)))
and put the results in column d I get this
00:02:25 (notice the far left 0 is gone)
00:08:40
however, I can't sum this. I'm assuming becuse column D is now the results
of the above formula.

this works fine if I goto column C2 and hit the F2 key and remove the 0
myself (but I have too many rows for that).

Really all I want to do is be able to sum column C.

Thanks for all the help (BTW, programmer I am not)!

ScottP
 
J

JE McGimpsey

Try (array-entered:CTR-SHIFT-ENTER or CMD-RETURN):

=SUM(--("0"&RIGHT(C1:C100,8)))
 
Top