Hi,

Would someone be kind enough to assist with code to automatically
convert:

data entry 1234 to 12:34 (12 mins:34 secs)

Thanks in advance

Bob

[MX9]

If value "1234" is in cell A1, put this formula in B1. =TIME(0,LEFT(A1,2),RIGHT(A1,2))

Format cell B1 to TIME and select format 13:30:55.

Note the formula assumes you always have a four-digit number to convert and that the first two digits are always minutes, last two always seconds. If this is not the case, such as a three-digit value like 930, the formula can be adjusted with an IF statement to take the minutes from the first digit only:

=IF(LEN(A1)=3,TIME(0,LEFT(A1,1),RIGHT(A1,2)),TIME(0,LEFT(A1,2),RIGHT(A1,2)))

Also, the result gives hours:min:sec, and since there are no hours, it comes out to 0:12:34. There are ways to get rid of the zero hours.

It's not the coding solution you asked for but I'm sure someone can combine the formula with other bits of coding to take advantage of it, or a coding only method to directly translate.

Hi Bob,

Assuming that cell A2 contains 1234 then
=TIMEVALUE("00:"&LEFT(TEXT(A1,"0000"),2)&":"&RIGHT(TEXT(A1,"0000"),2))

Custom format the cell containing the formula to the following
[mm]" mins":ss" secs"

The square brackets stops minute value 60 or greater becoming hours. For
example 6429 becomes 69 mins:29 secs. Up to you whether you use them but if
not then any values 60 or greater minutes will need format to include hours.

Using TEXT to format to 4 digits ensures that it works with say 142 to become
1 mins:42 secs.

The "00" at the leading end is dummy hours otherwise Excel thinks that it is
hrs and mins rather than mins and secs.


--
Regards,

OssieMac


"Ozbobeee" wrote:

> Hi,
>
> Would someone be kind enough to assist with code to automatically
> convert:
>
> data entry 1234 to 12:34 (12 mins:34 secs)
>
> Thanks in advance
>
> Bob
>

You can use this formula instead of the one you posted...

=--TEXT(A1,"00\:00\:00")

still using the same Custom Format you indicated.

--
Rick (MVP - Excel)


"OssieMac" <(E-Mail Removed)> wrote in message
news:FEABE7E9-557D-43BA-A38A-(E-Mail Removed)...
> Hi Bob,
>
> Assuming that cell A2 contains 1234 then
> =TIMEVALUE("00:"&LEFT(TEXT(A1,"0000"),2)&":"&RIGHT(TEXT(A1,"0000"),2))
>
> Custom format the cell containing the formula to the following
> [mm]" mins":ss" secs"
>
> The square brackets stops minute value 60 or greater becoming hours. For
> example 6429 becomes 69 mins:29 secs. Up to you whether you use them but
> if
> not then any values 60 or greater minutes will need format to include
> hours.
>
> Using TEXT to format to 4 digits ensures that it works with say 142 to
> become
> 1 mins:42 secs.
>
> The "00" at the leading end is dummy hours otherwise Excel thinks that it
> is
> hrs and mins rather than mins and secs.
>
>
> --
> Regards,
>
> OssieMac
>
>
> "Ozbobeee" wrote:
>
>> Hi,
>>
>> Would someone be kind enough to assist with code to automatically
>> convert:
>>
>> data entry 1234 to 12:34 (12 mins:34 secs)
>>
>> Thanks in advance
>>
>> Bob
>>

On Oct 24, 5:14*pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> You can use this formula instead of the one you posted...
>
> =--TEXT(A1,"00\:00\:00")
>
> still using the same Custom Format you indicated.
>
> --
> Rick (MVP - Excel)
>
> "OssieMac" <Ossie...@discussions.microsoft.com> wrote in message
>
> news:FEABE7E9-557D-43BA-A38A-(E-Mail Removed)...
>
> > Hi Bob,
>
> > Assuming that cell A2 contains 1234 then
> > =TIMEVALUE("00:"&LEFT(TEXT(A1,"0000"),2)&":"&RIGHT(TEXT(A1,"0000"),2))
>
> > Custom format the cell containing the formula to the following
> > [mm]" mins":ss" secs"
>
> > The square brackets stops minute value 60 or greater becoming hours. For
> > example 6429 becomes 69 mins:29 secs. Up to you whether you use them but
> > if
> > not then any values 60 or greater minutes will need format to include
> > hours.
>
> > Using TEXT to format to 4 digits ensures that it works with say 142 to
> > become
> > 1 mins:42 secs.
>
> > The "00" at the leading end is dummy hours otherwise Excel thinks that it
> > is
> > hrs and mins rather than mins and secs.
>
> > --
> > Regards,
>
> > OssieMac
>
> > "Ozbobeee" wrote:
>
> >> Hi,
>
> >> Would someone be kind enough to assist with code to automatically
> >> convert:
>
> >> data entry 1234 to 12:34 (12 mins:34 secs)
>
> >> Thanks in advance
>
> >> Bob

Thanks Fellas.

Cheers

Bob

Since you posted in programming here is a programming solution to convert the
number to time...

http://www.cpearson.com/excel/DateTimeEntry.htm
--
HTH...

Jim Thomlinson


"Ozbobeee" wrote:

> Hi,
>
> Would someone be kind enough to assist with code to automatically
> convert:
>
> data entry 1234 to 12:34 (12 mins:34 secs)
>
> Thanks in advance
>
> Bob
>

Hi Rick,

I really like your answer. However, my curiosity is never satisfied unless I
understand all the why's and wherefores. I Googled it trying to find more
info on your method and whether similar approach applies to any other
formatting but without success. I wonder if you can point me towards some
documentation to explain it please.

--
Regards,

OssieMac


"Rick Rothstein" wrote:

> You can use this formula instead of the one you posted...
>
> =--TEXT(A1,"00\:00\:00")
>
> still using the same Custom Format you indicated.
>
> --
> Rick (MVP - Excel)
>
>
> "OssieMac" <(E-Mail Removed)> wrote in message
> news:FEABE7E9-557D-43BA-A38A-(E-Mail Removed)...
> > Hi Bob,
> >
> > Assuming that cell A2 contains 1234 then
> > =TIMEVALUE("00:"&LEFT(TEXT(A1,"0000"),2)&":"&RIGHT(TEXT(A1,"0000"),2))
> >
> > Custom format the cell containing the formula to the following
> > [mm]" mins":ss" secs"
> >
> > The square brackets stops minute value 60 or greater becoming hours. For
> > example 6429 becomes 69 mins:29 secs. Up to you whether you use them but
> > if
> > not then any values 60 or greater minutes will need format to include
> > hours.
> >
> > Using TEXT to format to 4 digits ensures that it works with say 142 to
> > become
> > 1 mins:42 secs.
> >
> > The "00" at the leading end is dummy hours otherwise Excel thinks that it
> > is
> > hrs and mins rather than mins and secs.
> >
> >
> > --
> > Regards,
> >
> > OssieMac
> >
> >
> > "Ozbobeee" wrote:
> >
> >> Hi,
> >>
> >> Would someone be kind enough to assist with code to automatically
> >> convert:
> >>
> >> data entry 1234 to 12:34 (12 mins:34 secs)
> >>
> >> Thanks in advance
> >>
> >> Bob
> >>
>
>

I'm not aware of any documents that will explain it. Basically, the TEXT
function will distribute the digits in the integer portion of the number (in
case a floating point value is specified for the first argument),
right-justified, into the 0 or # (in front of the decimal point if there is
one) provided in the format pattern. We need to escape the colons (that is
what the back slash does, makes the colons pure text instead of them having
their special meaning to the TEXT function as time separators) in order to
be able to create a text string value that "looks" like a time value... the
double unary symbols in front of the TEXT function tells Excel to try and
make the text string into a numeric value if possible. Excel is very
versatile in making text strings into numbers and, in this case, it sees a
text string that looks like a time value, so it makes it into a time value.

--
Rick (MVP - Excel)


"OssieMac" <(E-Mail Removed)> wrote in message
news:CC7A4172-AE72-4CF6-9629-(E-Mail Removed)...
> Hi Rick,
>
> I really like your answer. However, my curiosity is never satisfied unless
> I
> understand all the why's and wherefores. I Googled it trying to find more
> info on your method and whether similar approach applies to any other
> formatting but without success. I wonder if you can point me towards some
> documentation to explain it please.
>
> --
> Regards,
>
> OssieMac
>
>
> "Rick Rothstein" wrote:
>
>> You can use this formula instead of the one you posted...
>>
>> =--TEXT(A1,"00\:00\:00")
>>
>> still using the same Custom Format you indicated.
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "OssieMac" <(E-Mail Removed)> wrote in message
>> news:FEABE7E9-557D-43BA-A38A-(E-Mail Removed)...
>> > Hi Bob,
>> >
>> > Assuming that cell A2 contains 1234 then
>> > =TIMEVALUE("00:"&LEFT(TEXT(A1,"0000"),2)&":"&RIGHT(TEXT(A1,"0000"),2))
>> >
>> > Custom format the cell containing the formula to the following
>> > [mm]" mins":ss" secs"
>> >
>> > The square brackets stops minute value 60 or greater becoming hours.
>> > For
>> > example 6429 becomes 69 mins:29 secs. Up to you whether you use them
>> > but
>> > if
>> > not then any values 60 or greater minutes will need format to include
>> > hours.
>> >
>> > Using TEXT to format to 4 digits ensures that it works with say 142 to
>> > become
>> > 1 mins:42 secs.
>> >
>> > The "00" at the leading end is dummy hours otherwise Excel thinks that
>> > it
>> > is
>> > hrs and mins rather than mins and secs.
>> >
>> >
>> > --
>> > Regards,
>> >
>> > OssieMac
>> >
>> >
>> > "Ozbobeee" wrote:
>> >
>> >> Hi,
>> >>
>> >> Would someone be kind enough to assist with code to automatically
>> >> convert:
>> >>
>> >> data entry 1234 to 12:34 (12 mins:34 secs)
>> >>
>> >> Thanks in advance
>> >>
>> >> Bob
>> >>
>>
>>