Time inputted as minutes and seconds Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

edbarunning - 19 May 2006 02:20 GMT

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

Signature

EDba

Reply to this Message

JE McGimpsey - 19 May 2006 07:18 GMT

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

> 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

Reply to this Message

Pat Garard - 19 May 2006 09:05 GMT

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.

Signature

Regards,
Pat Garard
Melbourne, Australia
_______________________

> 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

Reply to this Message

Pete_UK - 19 May 2006 13:14 GMT

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 to this Message