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

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

I have got solution for it:

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

Thank you for help anyway,

stremetzky