removing digit or calc LEN fields

[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

[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))


In article <[email protected]>,
"tri_p" <[email protected]> wrote:

> 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

[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

"JE McGimpsey" wrote:

> 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))
>
>
> In article <[email protected]>,
> "tri_p" <[email protected]> wrote:
>
> > 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
>

[JE McGimpsey]

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

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

In article <[email protected]>,
"tri_p" <[email protected]> wrote:

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