Minutes and Seconds

[Guest]

I want to enter a date as if I were denoting the minutes and seconds left in
a sporting event, i.e., 4:10 to represent 4 minutes and 10 seconds. I do NOT
want an hour to be applied to the time (ex: 3:04:10). I do not see a way to
do this with Excel time formatting options. Each one uses an
hour/minute/second format.

Help anyone?

 

[Max]

Try formatting as Custom> Type: [mm]:ss
(with square brackets [ ] around the minutes part: mm)
This'll allow rollover of minutes

 

[Ron Rosenfeld]

I want to enter a date as if I were denoting the minutes and seconds left in
a sporting event, i.e., 4:10 to represent 4 minutes and 10 seconds. I do NOT
want an hour to be applied to the time (ex: 3:04:10). I do not see a way to
do this with Excel time formatting options. Each one uses an
hour/minute/second format.

Help anyone?

You are correct. Formatting only affects how cell contents are displayed. It
does not affect how entries are interpreted.

1. You can enter the time as 0:4:10. (You can format it as m:ss or [m]:ss or
even as [m]" min "ss" sec" )
2. You could enter it with a different separator, and use a VBA event macro to
convert your entry to minutes:seconds


--ron

 

[Guest]

So, are you saying that there is no way to format a cell with a value of just
minutes and seconds (4:12)? i tried your formatting suggestions and Excel
placed an hour before the minutes and seconds each time. Even when I entered
the time as 0:4:12, it displayed 12:04:12 AM.

Thanks.

I want to enter a date as if I were denoting the minutes and seconds left in
a sporting event, i.e., 4:10 to represent 4 minutes and 10 seconds. I do NOT
want an hour to be applied to the time (ex: 3:04:10). I do not see a way to
do this with Excel time formatting options. Each one uses an
hour/minute/second format.

Help anyone?

You are correct. Formatting only affects how cell contents are displayed. It
does not affect how entries are interpreted.

1. You can enter the time as 0:4:10. (You can format it as m:ss or [m]:ss or
even as [m]" min "ss" sec" )
2. You could enter it with a different separator, and use a VBA event macro to
convert your entry to minutes:seconds


--ron

 

[Max]

Even when I entered the time as 0:4:12,
it displayed 12:04:12 AM.

Think you might not have formatted correctly ?

If you had formatted say col A
as suggested, viz. via:

Select col A
Click Format > Cells > Custom (under "Category")
In the "Type:" box, enter:
[mm]:ss
Click OK

And then entered the time in A1:
0:4:12

you would have got the display *in-cell* as:
04:12

And if you enter a time in A2
containing a non-zero "hour" part, say:
1:4:12

you would have the display *in-cell* in A2 as:
64:12

which is 64 minutes and 12 secs
(1 hour added to 4 minutes = 64 minutes)

 

[Ron Rosenfeld]

So, are you saying that there is no way to format a cell with a value of just
minutes and seconds (4:12)? i tried your formatting suggestions and Excel
placed an hour before the minutes and seconds each time. Even when I entered
the time as 0:4:12, it displayed 12:04:12 AM.

When you state "it displayed 12:04:12 AM" is that in the CELL or in the FORMULA
BAR?

If in the formula bar, there is nothing you can do to change that. (Although
you can hide the formula bar if you are concerned about the end-user).

Formatting only affects how the value is displayed in the CELL. It cannot
change how it is displayed in the formula bar.


--ron

 

[Harald Staff]

Hi

As others stated; formatting won't do. Two additional ideas:

1 In a helper column, divide by 60 to transform hh:mm into mm:ss

2 Rightclick sheet tab, choose "view code", paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range
For Each Cel In Target
If Cel.Column = 2 Then 'B column only
Select Case Cel.Value
Case 0 To 0.99999999
Case 1 To 99
Cel.Value = TimeSerial(0, 0, Cel.Value)
Cel.NumberFormat = "[mm]:ss"
Case 100 To 5959
Cel.Value = TimeSerial(0, Int(Cel.Value / 100), _
Cel.Value Mod 100)
Cel.NumberFormat = "[mm]:ss"
Case Else
Cel.Value = ""
End Select
End If
Next
End Sub

Now you can use simplified and fast time entry in your B column, no
separators needed. 12 becomes 00:00:12, 123 becomes 0:01:23 , formatted to
hide the hours .

HTH. Best wishes Harald