I direct a one-hour race walk event on a track and want to enter lap
times each athlete into an Excel spreadsheet. The times are in minutes
and second (MM:SS) and each athlete can complete as many 35 laps. I
calculate and display the cumulative and elapsed time for each lap.

Currently, I enter each time as 0:MM:SS. So, 54 minutes and 32 seconds
would be entered as 0:54:32. I'm not a very good typist and the extra
typing seems unnecessary. I would like to enter the time as 5432 and
have Excel convert my entry to the time format.

I'm a novice with Excel. What is the best way to use Excel to speed up
the data entry process?

Thank you. -- Justin

See Chip Pearson's site for code and instructions on entering dates and times
without separators.

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


Gord Dibben MS Excel MVP

On 21 Oct 2006 18:53:41 -0700, "jk" wrote:

I direct a one-hour race walk event on a track and want to enter lap
times each athlete into an Excel spreadsheet. The times are in minutes
and second (MM:SS) and each athlete can complete as many 35 laps. I
calculate and display the cumulative and elapsed time for each lap.

Currently, I enter each time as 0:MM:SS. So, 54 minutes and 32 seconds
would be entered as 0:54:32. I'm not a very good typist and the extra
typing seems unnecessary. I would like to enter the time as 5432 and
have Excel convert my entry to the time format.

I'm a novice with Excel. What is the best way to use Excel to speed up
the data entry process?

Thank you. -- Justin

Format the cells where you enter this time as TEXT. This will allow leading
zeros and if you *always* enter the time as a 4 digit string like:

5432 = 55m 32s
0105 = 1m 5s
0100 = 1m 0s
1000 = 10m 0s
0001 = 0m 1s
0010 = 0m 10s

Then use this formula to convert those strings into true Excel times:

=TIME(0,LEFT(A1,2),RIGHT(A1,2))

Format as h:mm:ss or mm:ss.

Biff

"jk" wrote in message
oups.com...
I direct a one-hour race walk event on a track and want to enter lap
times each athlete into an Excel spreadsheet. The times are in minutes
and second (MM:SS) and each athlete can complete as many 35 laps. I
calculate and display the cumulative and elapsed time for each lap.

Currently, I enter each time as 0:MM:SS. So, 54 minutes and 32 seconds
would be entered as 0:54:32. I'm not a very good typist and the extra
typing seems unnecessary. I would like to enter the time as 5432 and
have Excel convert my entry to the time format.

I'm a novice with Excel. What is the best way to use Excel to speed up
the data entry process?

Thank you. -- Justin

I like Biff's formula as I can use the output to calculate the split
times. I modified it slightly to allow input with fewer than 4
characters:

=IF(A1="","",TIME(0,RIGHT(LEFT(10000+A1,3),2);RIGH T(A1,2)))


Biff wrote:
Format the cells where you enter this time as TEXT. This will allow leading
zeros and if you *always* enter the time as a 4 digit string like:

5432 = 55m 32s
0105 = 1m 5s
0100 = 1m 0s
1000 = 10m 0s
0001 = 0m 1s
0010 = 0m 10s

Then use this formula to convert those strings into true Excel times:

=TIME(0,LEFT(A1,2),RIGHT(A1,2))

Format as h:mm:ss or mm:ss.

Biff

"jk" wrote in message
oups.com...
I direct a one-hour race walk event on a track and want to enter lap
times each athlete into an Excel spreadsheet. The times are in minutes
and second (MM:SS) and each athlete can complete as many 35 laps. I
calculate and display the cumulative and elapsed time for each lap.

Currently, I enter each time as 0:MM:SS. So, 54 minutes and 32 seconds
would be entered as 0:54:32. I'm not a very good typist and the extra
typing seems unnecessary. I would like to enter the time as 5432 and
have Excel convert my entry to the time format.

I'm a novice with Excel. What is the best way to use Excel to speed up
the data entry process?

Thank you. -- Justin

"Biff" wrote in message
...
Format the cells where you enter this time as TEXT. This will allow
leading
zeros and if you *always* enter the time as a 4 digit string like:

5432 = 55m 32s

Parallel universe? <G

5432 = 55m 32s

Parallel universe? <G

No. Just plain old...Doh!

Biff

"Bob Phillips" wrote in message
...

"Biff" wrote in message
...
Format the cells where you enter this time as TEXT. This will allow
leading
zeros and if you *always* enter the time as a 4 digit string like:

5432 = 55m 32s

Parallel universe? <G

Do I scent a laptop and a case for a clicking a command button then a name?

"jk" wrote in message
oups.com...
I direct a one-hour race walk event on a track and want to enter lap
times each athlete into an Excel spreadsheet. The times are in minutes
and second (MM:SS) and each athlete can complete as many 35 laps. I
calculate and display the cumulative and elapsed time for each lap.

Currently, I enter each time as 0:MM:SS. So, 54 minutes and 32 seconds
would be entered as 0:54:32. I'm not a very good typist and the extra
typing seems unnecessary. I would like to enter the time as 5432 and
have Excel convert my entry to the time format.

I'm a novice with Excel. What is the best way to use Excel to speed up
the data entry process?

Thank you. -- Justin

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

and format as "mm:ss"


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
oups.com...
I direct a one-hour race walk event on a track and want to enter lap
times each athlete into an Excel spreadsheet. The times are in minutes
and second (MM:SS) and each athlete can complete as many 35 laps. I
calculate and display the cumulative and elapsed time for each lap.

Currently, I enter each time as 0:MM:SS. So, 54 minutes and 32 seconds
would be entered as 0:54:32. I'm not a very good typist and the extra
typing seems unnecessary. I would like to enter the time as 5432 and
have Excel convert my entry to the time format.

I'm a novice with Excel. What is the best way to use Excel to speed up
the data entry process?

Thank you. -- Justin

Wow! I did not know you could do that. I thought the two minus signs
following the equal sign was an error until I played with it and
realized it was a sort of replacement character. I am not sure what the
backslash character does either, but it all seems to work. I tried to
Google with that syntax to learn more on what it does but did not luck.
Would you direct me to a description of that syntax?

Thank you. -- Justin


Bob Phillips wrote:
=--TEXT(A10,"00\:00\:00")

and format as "mm:ss"


--
HTH

Bob Phillips

Firstly, the backslash is used to tell Excel to treat the next character as
a embedded text character, so here I use to insert the colon (:) time
delimiters.

Regarding the two minus signs, or double unary, if you just use

=TEXT(A10,"00\:00\:00")

you will seemingly get the same result, but it will in fact be text, so you
cannot do math on it (add to it, sum it, etc.). To revert it back to a
number, you need to do some mathematical operation on it. You could add 0

=TEXT(A10,"00\:00\:00")+0

multiply it by 1

=TEXT(A10,"00\:00\:00")*1

or, my preference, apply the double unary

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

In this latter case, a single unary will change it to a number

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

but of course it will also negate it, so a second unary is used to restore
it back to its original condition

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jk" wrote in message
oups.com...
Wow! I did not know you could do that. I thought the two minus signs
following the equal sign was an error until I played with it and
realized it was a sort of replacement character. I am not sure what the
backslash character does either, but it all seems to work. I tried to
Google with that syntax to learn more on what it does but did not luck.
Would you direct me to a description of that syntax?

Thank you. -- Justin


Bob Phillips wrote:
=--TEXT(A10,"00\:00\:00")

and format as "mm:ss"


--
HTH

Bob Phillips

Thanks Bob. You explaination is clear and makes a lot of sense to me
now. -- Justin

Bob Phillips wrote:
Firstly, the backslash is used to tell Excel to treat the next character as
a embedded text character, so here I use to insert the colon (:) time
delimiters.

Regarding the two minus signs, or double unary, if you just use

=TEXT(A10,"00\:00\:00")

you will seemingly get the same result, but it will in fact be text, so you
cannot do math on it (add to it, sum it, etc.). To revert it back to a
number, you need to do some mathematical operation on it. You could add 0

=TEXT(A10,"00\:00\:00")+0

multiply it by 1

=TEXT(A10,"00\:00\:00")*1

or, my preference, apply the double unary

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

In this latter case, a single unary will change it to a number

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

but of course it will also negate it, so a second unary is used to restore
it back to its original condition

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

--
HTH

Bob Phillips