rapid input of MM:SS
 

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

rapid input of MM:SS
 

Hi Justin,

If the time is always entered as a 4 digit number representing mmss within
the range 1000 to 9959, it would be possible to convert this to time format
with e.g. in b1:
=TEXT("0:"&LEFT(a1,2)&":"&RIGHT(a1,2),"hh:mm:ss")
where 5432 is in a1.
To allow for hours 23 in the cumulative time, a custom format can be
created:
[h]:mm:ss (to be used instead of hh:mm:ss).

Hth
Anthony

"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

rapid input of MM:SS
 

ps.
If all 35 laps can be completed in 1 hour, 3 digits may be more appropriate,
from 100 to 959.
Or, 3 and 4 digits can be used at the same time, e.g. 959 is entered as
'0959, prefixed with an apostrophe, instead of as 959.

A

"Anthony D" wrote:

Hi Justin,

If the time is always entered as a 4 digit number representing mmss within
the range 1000 to 9959, it would be possible to convert this to time format
with e.g. in b1:
=TEXT("0:"&LEFT(a1,2)&":"&RIGHT(a1,2),"hh:mm:ss")
where 5432 is in a1.
To allow for hours 23 in the cumulative time, a custom format can be
created:
[h]:mm:ss (to be used instead of hh:mm:ss).

Hth
Anthony

"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

rapid input of MM:SS
 

I used your suggestions. They worked great!

All of the input times are less than one hour so I modified the formula
to avoid typing the apostrophe when the input contains less than 4
character.

=TEXT("0:"&RIGHT(LEFT(10000+a1,3),2)&":"&RIGHT(a1, 2),"hh:mm:ss")

Thank you for all your help. -- Justin

Anthony D wrote:
ps.
If all 35 laps can be completed in 1 hour, 3 digits may be more appropriate,
from 100 to 959.
Or, 3 and 4 digits can be used at the same time, e.g. 959 is entered as
'0959, prefixed with an apostrophe, instead of as 959.

A

"Anthony D" wrote:

Hi Justin,

If the time is always entered as a 4 digit number representing mmss within
the range 1000 to 9959, it would be possible to convert this to time format
with e.g. in b1:
=TEXT("0:"&LEFT(a1,2)&":"&RIGHT(a1,2),"hh:mm:ss")
where 5432 is in a1.
To allow for hours 23 in the cumulative time, a custom format can be
created:
[h]:mm:ss (to be used instead of hh:mm:ss).

Hth
Anthony

"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

rapid input of MM:SS
 

Format cells as: 0\:00\:00

"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

rapid input of MM:SS
 

Great !
Thank you for your feedback Justin,
glad to be of help.
Teethless mama has a nice alternative formatting shortcut as well with
0\:00\:00 (or 0":"00":"00)

Anthony

"jk" wrote:

I used your suggestions. They worked great!

All of the input times are less than one hour so I modified the formula
to avoid typing the apostrophe when the input contains less than 4
character.

=TEXT("0:"&RIGHT(LEFT(10000+a1,3),2)&":"&RIGHT(a1, 2),"hh:mm:ss")

Thank you for all your help. -- Justin

Anthony D wrote:
ps.
If all 35 laps can be completed in 1 hour, 3 digits may be more appropriate,
from 100 to 959.
Or, 3 and 4 digits can be used at the same time, e.g. 959 is entered as
'0959, prefixed with an apostrophe, instead of as 959.

A

"Anthony D" wrote:

Hi Justin,

If the time is always entered as a 4 digit number representing mmss within
the range 1000 to 9959, it would be possible to convert this to time format
with e.g. in b1:
=TEXT("0:"&LEFT(a1,2)&":"&RIGHT(a1,2),"hh:mm:ss")
where 5432 is in a1.
To allow for hours 23 in the cumulative time, a custom format can be
created:
[h]:mm:ss (to be used instead of hh:mm:ss).

Hth
Anthony

"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