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