I'm trying to conver 425033 to 42:50:30
I'm running out of steam!
I'm trying to conver 425033 to 42:50:30
I'm running out of steam!
You can parse the string with
=LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2)
Dave
"doveness" <[email protected]> wrote in message
news:[email protected]...
> I'm trying to conver 425033 to 42:50:30
>
> I'm running out of steam!
Dave's formula will return a text result which will look like a time.
If you want the result in true Excel time format (numeric) then you
will have to put VALUE( ... ) around his formula and format the cell
using a custom format of [hh]:mm:ss.
Hope this helps.
Pete
Hi Dave:
Good answer.
A slight variation will give a time in standard numerical format:
=LEFT(A1,2)/24+MID(A1,3,2)/(24*60)+RIGHT(A1,2)/(24*60*60)
format as [hh]:mm:ss
--
Gary's Student
"Dave Sheldon" wrote:
> You can parse the string with
> =LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2)
>
> Dave
>
> "doveness" <[email protected]> wrote in message
> news:[email protected]...
> > I'm trying to conver 425033 to 42:50:30
> >
> > I'm running out of steam!
>
>
>
If you have hrs between 1 and 10 you will have only 5 numbers and only the
RIGHT formula will give correct answer.
Then you have to modify your formula like this:
=IF(LEN(A1)=5;"0"&LEFT(A1;1)&":"&MID(A1;2;2)&":"&RIGHT(A1;2);LEFT(A1;2)&":"&MID(A1;3;2)&":"&RIGHT(A1;2))
if below 1 hrs you maybe have only 4.
Then you have to modify even further:
=IF(LEN(A1)=4;"00"&":"&MID(A1;1;2)&":"&RIGHT(A1;2);IF(LEN(A1)=5;"0"&LEFT(A1;1)&":"&MID(A1;2;2)&":"&RIGHT(A1;2);LEFT(A1;2)&":"&MID(A1;3;2)&":"&RIGHT(A1;2)))
*gublues
Gary''s Student skrev:
> Hi Dave:
>
> Good answer.
>
> A slight variation will give a time in standard numerical format:
>
> =LEFT(A1,2)/24+MID(A1,3,2)/(24*60)+RIGHT(A1,2)/(24*60*60)
> format as [hh]:mm:ss
> --
> Gary's Student
>
>
> "Dave Sheldon" wrote:
>
> > You can parse the string with
> > =LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2)
> >
> > Dave
> >
> > "doveness" <[email protected]> wrote in message
> > news:[email protected]...
> > > I'm trying to conver 425033 to 42:50:30
> > >
> > > I'm running out of steam!
> >
> >
> >
A simpler way.....
=TEXT(A1,"00\:00\:00")+0
format as [h]:mm:ss
Your comments are correct. The formula is designed to handle 6 digit
quantities that can be mapped: hhmmss
It will fail for hours less than 10.
It will fail for hours greater than 99.
The formula will, however, handle numbers as per the OP's spec.
--
Gary's Student
"gublues" wrote:
> If you have hrs between 1 and 10 you will have only 5 numbers and only the
> RIGHT formula will give correct answer.
> Then you have to modify your formula like this:
> =IF(LEN(A1)=5;"0"&LEFT(A1;1)&":"&MID(A1;2;2)&":"&RIGHT(A1;2);LEFT(A1;2)&":"&MID(A1;3;2)&":"&RIGHT(A1;2))
>
> if below 1 hrs you maybe have only 4.
> Then you have to modify even further:
> =IF(LEN(A1)=4;"00"&":"&MID(A1;1;2)&":"&RIGHT(A1;2);IF(LEN(A1)=5;"0"&LEFT(A1;1)&":"&MID(A1;2;2)&":"&RIGHT(A1;2);LEFT(A1;2)&":"&MID(A1;3;2)&":"&RIGHT(A1;2)))
>
> *gublues
>
>
> Gary''s Student skrev:
>
> > Hi Dave:
> >
> > Good answer.
> >
> > A slight variation will give a time in standard numerical format:
> >
> > =LEFT(A1,2)/24+MID(A1,3,2)/(24*60)+RIGHT(A1,2)/(24*60*60)
> > format as [hh]:mm:ss
> > --
> > Gary's Student
> >
> >
> > "Dave Sheldon" wrote:
> >
> > > You can parse the string with
> > > =LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2)
> > >
> > > Dave
> > >
> > > "doveness" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I'm trying to conver 425033 to 42:50:30
> > > >
> > > > I'm running out of steam!
> > >
> > >
> > >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks