Preformat cells as Text in Column A
Create a helper Column B

In B1: =IF(A1="","",TIME(0,LEFT(A1,FIND(":",A1)-1),RIGHT(A1,FIND(":",A1)-1)))

Format cell as [m]:ss


"LurfysMa" wrote:

I have some timed data to enter. Each entry has two values: (1) number
of repetitions and (2) elapsed time in minutes and seconds (mm:ss).

Some sample data is:

Reps mm:ss
25 3:22
202 15:00
99 11:30
1405 77:22

I want to enter the data just as shown above. Note that the minutes
may exceed 59. That is, time over an hour is not shown as hh:mm:ss.

I want Excel to calculate the number of repetitions/hour.

Reps mm:ss rph
25 3:22 446
202 15:00 808
99 11:30 517
1405 77:22 1090

The calculations are working correctly, but I am having trouble with
the data entry.

1. If I enter "3:22", it goes in as "03:22:00" = 3 hours and 22
minutes, not 3 minutes and 22 seconds. I can enter "0:03:22" and it
works, but I'd prefer not to have to enter the hours.

Is there a way for me to enter "3:22" and have it go it as 3 minutes
and 22 seconds?

2. All times are in minutes and seconds, even though the total time
may be over an hour. If I enter the last entry as "00:77:22", it takes
the correct value, but it changes the cell formatting "General". If I
change it back to "mm:ss", I get "17:22".

Assuming I get an answer to #1 and can enter "77:22", is there a way
for me to format it to display as "77:22"?

Thanks

--
Running Excel 2000 SP-3 on Windows 2000