Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Time inputted as minutes and seconds Excel
When inputting time that is in minutes and seconds I have to use a decimal
and a zero. i.e. mm:ss.o. Can I reformat so I can input as mm:ss -- EDba |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Time inputted as minutes and seconds Excel
No. Formatting has no effect on how XL parses the entry (unless you set
the format to Text, in which case you won't get an XL time). You could use an event macro to divide your entry by 60. Here's one way. Put this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(.Cells, Range("A1:A10")) Is Nothing Then If IsNumeric(.Value) Then Application.EnableEvents = False .Value = .Value / 60 Application.EnableEvents = True .NumberFormat = "mm:ss" End If End If End With End Sub In article , edbarunning wrote: When inputting time that is in minutes and seconds I have to use a decimal and a zero. i.e. mm:ss.o. Can I reformat so I can input as mm:ss |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Time inputted as minutes and seconds Excel
G'Day Ed,
Regardless of cell formatting, Excel anticipates time as hh:mm:ss[.0]. If you ENTER 22:30, Excel assumes that the input is hh:mm (10:10 pm). To 'advise' Excel otherwise you must ENTER EITHER 00:22:30 OR 22:30.0 (12:22:30 AM for both). The cell formatting determines only how the time is displayed. -- Regards, Pat Garard Melbourne, Australia _______________________ "edbarunning" wrote in message ... When inputting time that is in minutes and seconds I have to use a decimal and a zero. i.e. mm:ss.o. Can I reformat so I can input as mm:ss -- EDba |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Time inputted as minutes and seconds Excel
If I am entering a number of times as minutes and seconds, I prefer to
use the numeric keypad and enter them using a decimal point rather than the colon (as Pat says, you have to enter 0:minutes:seconds, which is a bit tedious). You can then convert this into acceptable time format in another column. So, for example, assume you enter your data into column C, starting at C2, then put this formula in D2: =VALUE("0:"&INT(C2)&":"&MOD(C2,1)*100) and format the cell using custom set to [m]:ss. You can then copy this formula down several rows. Now if you have a time like 10:33, you enter 10.33 in column C and this will be converted to time format in column D. Once you have entered the data, you can fix the values in column D using paste special and values, and then delete column C. Hope this helps. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average time for a race in minutes seconds & tenths | Excel Worksheet Functions | |||
How do I sum increments of minutes and seconds | Excel Worksheet Functions | |||
how do I add minutes and seconds together in excel | Excel Worksheet Functions | |||
Convert "Time Interval" in "hours : minutes : seconds" to seconds | New Users to Excel | |||
Convert seconds to minutes and seconds in excel | Excel Worksheet Functions |