Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Atlanta
    Posts
    568
    Thanks
    7
    Thanked 1 Time in 1 Post

    Entering Times Quickly (XP)

    I need to enter a lot of times, like 8AM or 9PM. These are entered with a lot of other numbers so I use the number pad. However, I have to slow down and enter times as 8:00 (8AM) or 21:00 (9PM). Is there a way to avoid having to use the difficult colon when entering times?
    Ronny Richardson

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Entering Times Quickly (XP)

    Hi Ronnie

    You could use this User Defined Function to convert the times you put in.

    Assuming the times start in Cell A1 and continue down column A, type in the times as 2100,800,2300,730 etc:

    <pre>Sub Convert()
    Dim i As Integer
    Dim datValue As String
    Dim datNew As String

    i = Range("A" & Rows.Count).End(xlUp).Row

    For n = 1 To i

    datValue = Cells(n, 1).Value

    If Len(datValue) < 4 Then

    datNew = "0" & Left(datValue, 1) & ":" & Right(datValue, 2)

    Else

    datNew = Left(datValue, 2) & ":" & Right(datValue, 2)


    End If
    Cells(n, 1).Value = datNew
    Next n


    End Sub</pre>

    Jerry

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Entering Times Quickly (XP)

    Hi Jerry
    The attached code is a recommended adaptation of yours which:
    <UL><LI>allows any number of characters to be input;
    <LI>tests for and highlights any invalid data;
    <LI>converts 2400 to 0000; and.
    <LI>allows the macro to be run repeatedly without disturbing the data that was previously processed.[/list]
    Attached Files Attached Files
    Regards
    Don

  4. #4
    4 Star Lounger
    Join Date
    Feb 2006
    Location
    Cape Cod, Massachusetts, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Entering Times Quickly (XP)

    Ronny,
    You might also take a look at this discussion

    Paul

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Entering Times Quickly (XP)

    Hi Ronny,

    Here's some code that might make things easier. Essentially, it takes a cell input like '21..5' (or 21..05), '13..37' or '5..59' and converts it to '21:05', '13:37' or '5:59', respectively. In other words, instead of having to enter ':' as a time designator, you can enter '..'.<pre>Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Static oCell As Range
    If oCell Is Nothing Then Set oCell = ActiveCell
    If InStr(Range(oCell.Address).Value, "..") > 0 Then
    If Len(Range(oCell.Address).Value) < 6 Then
    If InStr(Range(oCell.Address).Value, "..") = 2 Or InStr(Range(oCell.Address).Value, "..") = 3 _
    Then Range(oCell.Address).Value = Replace(Range(oCell.Address).Value, "..", ":")
    ElseIf Len(Range(oCell.Address).Value) = 6 Then
    If InStr(Range(oCell.Address).Value, "..") = 3 _
    Then Range(oCell.Address).Value = Replace(Range(oCell.Address).Value, "..", ":")
    End If
    End If
    Set oCell = ActiveCell
    End Sub</pre>

    Notes:
    1. The code goes in your workbook's 'This Workbook' module.
    2. You must tab out of the input cell or select another cell on the same worksheet before the macro will execute.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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