Data Validation : Allow only time input
Results 1 to 4 of 4

Thread: Data Validation : Allow only time input
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Data Validation : Allow only time input

    I have a form that different teams return to me giving their total hours spent each day on different activities. Currently some people for example 1 and a half hours work type 1.5 or some do 1:30.
    I wish to limit input to time format ie 1:30.
    However these totals can go over 24 hours. The cell format is already [h]:mm and I want an error message if they were to type in 1.5.
    Iíve tried different things such as 10/02/07 15:00 but it still allows eg 1.5 to be input.
    Not sure if the validation would be that : needed to be input rather that looking at actual time formatting.
    Iíve searched different threads and not found an answer.
    Iím using 2007.
    Any help is gratefully received.

  2. #2
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation : Allow only time input

    Why not let them input as they wish and convert 1.50 to 1:30 in a change event?

  3. #3
    New Member
    Join Date
    Jul 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Data Validation : Allow only time input

    This is what I have to do now, but there is always an element of doubt that 1.5 is actually 1:30 or is it 1 hour 50 minutes. I wish to remove that doubt and force just time format input. If possible.

  4. #4
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,964
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Data Validation : Allow only time input

    Try this:
    Put the code in the sheet's code module. Change 'Range("A2:A10")' to suit.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
            If Target.Cells.Count = 1 Then
            
                If Target.Text Like "#:##" Or Target.Text Like "##:##" Then
                'do nothing
                Else
                Target.Activate
                MsgBox "You must use this format 'hour:minutes', example:  2:05" & vbCr & _
                "(it means 2 hour & 5 minutes)"
                End If
            End If
        End If
    End Sub
    Note: if the total hours could be more than 99 hour then we need to modify the code.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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