custom validation formula with 2 condition

[faizee]

I want to validate cell C7, with two coditions,
user can only enter Data either in time format (5:00, 19:56) or "Absent"

 

[Vidar]

Hi Faizee
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Address = "$C$7" Then
        With Target
            If .Value = "" Then
                On Error GoTo 0
                Exit Sub
            End If
            If Not ((.Value < 1 And Format(.Value, "00:00:00") Like "##:##:##") Or LCase(.Value) = "absent") Then
                Application.EnableEvents = False
                MsgBox "Wrong input, try again", vbOKOnly
                Target.Value = ""
                Application.EnableEvents = True
            End If
        End With
    End If
    On Error GoTo 0
End Sub

Vidar

 

[faizee]

please help in in simple formula,,
i have to copy it in whole table column,
can it be possible without VBA

 

[Vidar]

Hi
You can use Conditional formatting on the actual cell:
=OR(A1<1;A1="Absent")
First color the cell red, then in Conditional Format use "No Fill".

Vidar

 

[T. Valko]

Hi
You can use Conditional formatting on the actual cell:
=OR(A1<1;A1="Absent")
First color the cell red, then in Conditional Format use "No Fill".

Vidar

You also need to make sure the cell value is not less than 0.

=OR(AND(A1>=0,A1<1),A1="Absent")

If the OP wants to make sure the time value is entered in a specific format then some form of VBA (or a macro function) will be needed.

 

[faizee]

thank you both of you

 

[T. Valko]

thank you both of you

You're welcome!