+ Reply to Thread
Results 1 to 4 of 4

Time inputted as minutes and seconds Excel

  1. #1
    edbarunning
    Guest

    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. #2
    JE McGimpsey
    Guest

    Re: 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 <CA142187-6301-4233-9FD6-6A0BA3C55520@microsoft.com>,
    edbarunning <edbarunning@discussions.microsoft.com> 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. #3
    Pat Garard
    Guest

    Re: 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" <edbarunning@discussions.microsoft.com> wrote in message
    news:CA142187-6301-4233-9FD6-6A0BA3C55520@microsoft.com...
    > 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. #4
    Pete_UK
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1