Time inputted as minutes and seconds Excel

[Guest]

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

 

[JE McGimpsey]

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

 

[Pat Garard]

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.

 

[Pete_UK]

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