rapid input of MM:SS

[jk]

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

 

[Gord Dibben]

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

 

[Biff]

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

 

[David F Cox]

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

 

[jk]

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);RIGHT(A1,2)))

 

[Bob Phillips]

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>

 

[Bob Phillips]

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

and format as "mm:ss"


--
HTH

Bob Phillips

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

 

[jk]

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]

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)

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

 

[jk]

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

 

[Biff]

5432 = 55m 32s

Parallel universe? <G>

No. Just plain old...Doh!

Biff