Showing time as minutes

  • Thread starter Thread starter LurfysMa
  • Start date Start date
L

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
 
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
 
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
Click to expand...

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. ;-)
 
LurfysMa said:
So there's no built-in formatting codes to do whatr I want?
Click to expand...

No - formatting does *nothing* to change how inputs are parsed (except
that setting format to Text bypasses the parser entirely).
 
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
 
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.
 
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
Click to expand...

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.
 
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.
Click to expand...

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.
 
LurfysMa said:
Why does that custom formatting work? I thought the brackets were for
conditional formatting?
Click to expand...

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

[Red][<-100]-0;[Yellow][<0]0;[Green]0;@
Where is this documented?
Click to expand...

XL Help "About custom number formats"
 
Back
Top