Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting Hours and Minutes | Excel Discussion (Misc queries) | |||
How do I enter minutes and seconds (lap times)? | Excel Discussion (Misc queries) | |||
converting from digital degrees to degrees minutes seconds | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions | |||
Minutes to seconds - how to convert? | Excel Worksheet Functions |