Showing time as minutes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LurfysMa - 24 Feb 2007 15:55 GMT

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

Reply to this Message

JE McGimpsey - 24 Feb 2007 16:37 GMT

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

> 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).
[quoted text clipped - 40 lines]
> --
> Running Excel 2000 SP-3 on Windows 2000

Reply to this Message

LurfysMa - 24 Feb 2007 16:53 GMT

>One way:
>
[quoted text clipped - 11 lines]
> 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. ;-)

--
Running Excel 2000 SP-3 on Windows 2000

Reply to this Message

JE McGimpsey - 24 Feb 2007 19:23 GMT

> 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).

Reply to this Message

daddylonglegs - 24 Feb 2007 21:30 GMT

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.

> > 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).

Reply to this Message

LurfysMa - 25 Feb 2007 04:07 GMT

>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.

--
Running Excel 2000 SP-3 on Windows 2000

Reply to this Message

Teethless mama - 24 Feb 2007 20:32 GMT

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

Reply to this Message

LurfysMa - 25 Feb 2007 04:05 GMT

>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 "[s]" will show total seconds, too.

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

--
Running Excel 2000 SP-3 on Windows 2000

Reply to this Message

JE McGimpsey - 25 Feb 2007 04:47 GMT

> 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"

Reply to this Message