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 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" wrote in message
...
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"
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" wrote in message

...

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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