Convert data entry to mins:secs

[Ozbobeee]

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]

Time Convert

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.

 

[OssieMac]

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.

 

[Rick Rothstein]

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)

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

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

 

[Ozbobeee]

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)

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.

Thanks Fellas.

Cheers

Bob

 

[Jim Thomlinson]

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

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

 

[OssieMac]

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

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)

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

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

 

[Rick Rothstein]

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)

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

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)

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


:

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