Showing time as minutes

[LurfysMa]

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

 

[JE McGimpsey]

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If InStr(.NumberFormat, ":") Then
On Error Resume Next
Application.EnableEvents = False
.Value = .Value / 60
Application.EnableEvents = True
On Error GoTo 0
.NumberFormat = "[m]:ss"
End If
End With
End Sub

 

[LurfysMa]

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If InStr(.NumberFormat, ":") Then
On Error Resume Next
Application.EnableEvents = False
.Value = .Value / 60
Application.EnableEvents = True
On Error GoTo 0
.NumberFormat = "[m]:ss"
End If
End With
End Sub

Thanks for the macro code. I was afraid that that was what I would
have to do.

So there's no built-in formatting codes to do whatr I want?

Thanks for the code. I'll try it out. At least then I'll have total
control. ;-)

 

[JE McGimpsey]

So there's no built-in formatting codes to do whatr I want?

No - formatting does *nothing* to change how inputs are parsed (except
that setting format to Text bypasses the parser entirely).

 

[Guest]

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

 

[Guest]

Does it matter if the time is entered as hours? If you format cells as [h]:mm
and enter 77:22 then it will look exactly as you want and you can adjust the
formula in your rph column (multiply by 60) to get the answer you require.

 

[LurfysMa]

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

Why does that custom formatting work? I thought the brackets were for
conditional formatting? Where is this documented?

It turns out that "" will show total seconds, too.

So many hidden goodies in Excel. It's like a scavenger hunt.

 

[LurfysMa]

Does it matter if the time is entered as hours? If you format cells as [h]:mm
and enter 77:22 then it will look exactly as you want and you can adjust the
formula in your rph column (multiply by 60) to get the answer you require.

Now that's a clever solution. I was tempted by it, but I bet I would
forget that I was using nn:nn as mm:ss even though Excel considers it
hh:mm. I am impressed by the outside-the-box thinking, though.

 

[JE McGimpsey]

Why does that custom formatting work? I thought the brackets were for
conditional formatting?

Brackets can be used in custom (not conditional) formats, e.g.,

[Red][<-100]-0;[Yellow][<0]0;[Green]0;@

Where is this documented?

XL Help "About custom number formats"