Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
bob
 
Posts: n/a
Default Minutes and Seconds

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?
  #2   Report Post  
Max
 
Posts: n/a
Default

Try formatting as Custom Type: [mm]:ss
(with square brackets [ ] around the minutes part: mm)
This'll allow rollover of minutes
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"bob" wrote in message
...
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?



  #3   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Sun, 28 Nov 2004 17:11:03 -0800, bob wrote:

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
  #4   Report Post  
bob
 
Posts: n/a
Default

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.

"Ron Rosenfeld" wrote:

On Sun, 28 Nov 2004 17:11:03 -0800, bob wrote:

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

  #5   Report Post  
Max
 
Posts: n/a
Default

"bob" wrote
....
Even when I entered the time as 0:4:12,=20
it displayed 12:04:12 AM.


Think you might not have formatted correctly ?

If you had formatted say col A=20
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:=20
0:4:12

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

And if you enter a time in A2=20
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 =3D 64 minutes)

--
Rgds
Max
xl 97
---
GMT+8, 1=B0 22' N 103=B0 45' E
xdemechanik <atyahoo<dotcom
----


  #6   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Sun, 28 Nov 2004 21:05:02 -0800, bob wrote:

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
  #7   Report Post  
Harald Staff
 
Posts: n/a
Default

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




"bob" skrev i melding
...
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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formatting Hours and Minutes Anthony Slater Excel Discussion (Misc queries) 2 January 20th 05 03:03 PM
How do I enter minutes and seconds (lap times)? SeanP Excel Discussion (Misc queries) 1 January 18th 05 11:54 PM
converting from digital degrees to degrees minutes seconds Patricio Boric Excel Worksheet Functions 1 November 28th 04 08:43 PM
time formatting and time categorizing (vlookup or data validation) MarianneR Excel Worksheet Functions 4 November 18th 04 04:24 PM
Minutes to seconds - how to convert? Tony Moffat Excel Worksheet Functions 0 November 15th 04 12:42 AM


All times are GMT +1. The time now is 11:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"