Data Validation : Allow only time input

[Neil Hall]

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.

 

[theBardd]

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

 

[Neil Hall]

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.

 

[Akuini]

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

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.