I have imorted values from another program. Imported values are in
000:00 form (000 for hours and 00 for minutes). If there is less than
100 hours the value is still not treated as [h]:mm. For example:

029:15

How can I erase the first one or two zeros??

stremetzky

Make sure you don't have text. check for signgle quote att beginning of time.

"stremetzky" wrote:

I have imorted values from another program. Imported values are in
000:00 form (000 for hours and 00 for minutes). If there is less than
100 hours the value is still not treated as [h]:mm. For example:

029:15

How can I erase the first one or two zeros??

stremetzky

I think you'll find that it will default to text, without the need for a
single quote. A number of the normal tricks for converting to time won't
work.

If all else fails, try
=LEFT(A1,FIND(":",A1)-1)/24+RIGHT(A1,LEN(A1)-FIND(":",A1))/24/60
(or, if the number of characters is fixed, =LEFT(A1,3)/24+RIGHT(A1,2)/24/60)
and format as [h]:mm
--
David Biddulph

"Joel" wrote in message
...
Make sure you don't have text. check for signgle quote att beginning of
time.

"stremetzky" wrote:

I have imorted values from another program. Imported values are in
000:00 form (000 for hours and 00 for minutes). If there is less than
100 hours the value is still not treated as [h]:mm. For example:

029:15

How can I erase the first one or two zeros??

stremetzky

I think they want time, and got text instead. They need to convert from text
tto time.

"David Biddulph" wrote:

I think you'll find that it will default to text, without the need for a
single quote. A number of the normal tricks for converting to time won't
work.

If all else fails, try
=LEFT(A1,FIND(":",A1)-1)/24+RIGHT(A1,LEN(A1)-FIND(":",A1))/24/60
(or, if the number of characters is fixed, =LEFT(A1,3)/24+RIGHT(A1,2)/24/60)
and format as [h]:mm
--
David Biddulph

"Joel" wrote in message
...
Make sure you don't have text. check for signgle quote att beginning of
time.

"stremetzky" wrote:

I have imorted values from another program. Imported values are in
000:00 form (000 for hours and 00 for minutes). If there is less than
100 hours the value is still not treated as [h]:mm. For example:

029:15

How can I erase the first one or two zeros??

stremetzky

Can you suggest an easier way of converting 029:15 from text to time, Joel?
I feel there ought to be an easier way, but the usual ways don't seem to
work in this case.
--
David Biddulph

"Joel" wrote in message
...
I think they want time, and got text instead. They need to convert from
text
tto time.

"David Biddulph" wrote:

I think you'll find that it will default to text, without the need for a
single quote. A number of the normal tricks for converting to time won't
work.

If all else fails, try
=LEFT(A1,FIND(":",A1)-1)/24+RIGHT(A1,LEN(A1)-FIND(":",A1))/24/60
(or, if the number of characters is fixed,
=LEFT(A1,3)/24+RIGHT(A1,2)/24/60)
and format as [h]:mm
--
David Biddulph

"Joel" wrote in message
...
Make sure you don't have text. check for signgle quote att beginning
of
time.

"stremetzky" wrote:

I have imorted values from another program. Imported values are in
000:00 form (000 for hours and 00 for minutes). If there is less than
100 hours the value is still not treated as [h]:mm. For example:

029:15

How can I erase the first one or two zeros??

stremetzky

Not sure if this is any better. the real problem is sttripping off leading
zeros

=TIMEVALUE(trimchar(E16,"0"))

wrote my own function to strip off leading zeroes

Function trimchar(MyString As String, MyChar As String) As String

trimchar = MyString
Do While Left(trimchar, 1) = MyChar
trimchar = Mid(trimchar, 2)
Loop
End Function



"David Biddulph" wrote:

Can you suggest an easier way of converting 029:15 from text to time, Joel?
I feel there ought to be an easier way, but the usual ways don't seem to
work in this case.
--
David Biddulph

"Joel" wrote in message
...
I think they want time, and got text instead. They need to convert from
text
tto time.

"David Biddulph" wrote:

I think you'll find that it will default to text, without the need for a
single quote. A number of the normal tricks for converting to time won't
work.

If all else fails, try
=LEFT(A1,FIND(":",A1)-1)/24+RIGHT(A1,LEN(A1)-FIND(":",A1))/24/60
(or, if the number of characters is fixed,
=LEFT(A1,3)/24+RIGHT(A1,2)/24/60)
and format as [h]:mm
--
David Biddulph

"Joel" wrote in message
...
Make sure you don't have text. check for signgle quote att beginning
of
time.

"stremetzky" wrote:

I have imorted values from another program. Imported values are in
000:00 form (000 for hours and 00 for minutes). If there is less than
100 hours the value is still not treated as [h]:mm. For example:

029:15

How can I erase the first one or two zeros??

stremetzky

Not sure if this is any better. the real problem is sttripping off
leading
zeros

=TIMEVALUE(trimchar(E16,"0"))

wrote my own function to strip off leading zeroes

Function trimchar(MyString As String, MyChar As String) As String

trimchar = MyString
Do While Left(trimchar, 1) = MyChar
trimchar = Mid(trimchar, 2)
Loop
End Function

You can do this directly with worksheet formulas...

=TIMEVALUE(SUBSTITUTE(TRIM(SUBSTITUTE(C2,"0"," "))," ", "0"))

Rick

That seems to return 5:15, not 29:15 ?
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...

You can do this directly with worksheet formulas...

=TIMEVALUE(SUBSTITUTE(TRIM(SUBSTITUTE(C2,"0"," "))," ", "0"))

Not sure if this is any better. the real problem is sttripping off
leading
zeros

=TIMEVALUE(trimchar(E16,"0"))

wrote my own function to strip off leading zeroes

Function trimchar(MyString As String, MyChar As String) As String

trimchar = MyString
Do While Left(trimchar, 1) = MyChar
trimchar = Mid(trimchar, 2)
Loop
End Function

I think it returns what Joel's formula returns, which is all I was trying to
do. HOWEVER, the formula I posted has a much bigger problem... it doesn't
handle trailing zeroes correctly... I would need a LTRIM function to do
that, but Excel does not have one. So, my entire post should be ignored.

Rick


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
That seems to return 5:15, not 29:15 ?
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...

You can do this directly with worksheet formulas...

=TIMEVALUE(SUBSTITUTE(TRIM(SUBSTITUTE(C2,"0"," "))," ", "0"))

Not sure if this is any better. the real problem is sttripping off
leading
zeros

=TIMEVALUE(trimchar(E16,"0"))

wrote my own function to strip off leading zeroes

Function trimchar(MyString As String, MyChar As String) As String

trimchar = MyString
Do While Left(trimchar, 1) = MyChar
trimchar = Mid(trimchar, 2)
Loop
End Function

"David Biddulph" <groups [at] biddulph.org.uk wrote...
Can you suggest an easier way of converting 029:15 from text to
time, Joel?
I feel there ought to be an easier way, but the usual ways don't
seem to work in this case.
....

=LOOKUP(1E6,--MID(A1,{3;2;1},16))

That seems to return 5:15, not 29:15 ?
--
I think it returns what Joel's formula returns, which is all I was trying
to do. HOWEVER, the formula I posted has a much bigger problem... it
doesn't handle trailing zeroes correctly... I would need a LTRIM function
to do that, but Excel does not have one. So, my entire post should be
ignored.

Okay, first let me state that I am not entirely sure what final result the
OP was looking for; however, if all that is wanted is to return a text
string without leading zeroes (and which handles trailing zeroes
correctly<g), then I think this does that...

=SUBSTITUTE(TEXT(SUBSTITUTE(C2,":","."),"#.00"),". ",":")

Rick

On May 22, 4:02 am, Harlan Grove wrote:

=LOOKUP(1E6,--MID(A1,{3;2;1},16))

Very elegant, Harlan.

I have got solution for it:

=LEFT(A1)/24*100+RIGHT(A1,5)

Thank you for help anyway,

stremetzky