Checking if time is between 2-4 hours after another time

Assuming the times will be on the same day then try this

=IF(AND((B2-A2)*24>=2,(B2-A2)*24<=4),"Yes","No")

Hello,

I keep getting a "missing then or go to" message:

I'm doing it in VBA though, and the code is:

 If Cells(Target.Row, 2).Value >= 4 Then

 If DateDiff("n", Cells(Target.Row, 1).Value, Cells(Target.Row, 3).Value) <= 30 And DateDiff("h", Cells(Target.Row, 3).Value, Cells(Target.Row, 4).Value) >= 2 And DateDiff("h", Cells(Target.Row, 3).Value, Cells(Target.Row, 4).Value) <= 4, "Yes","No")
 then
 Cells(Target.Row, 5).Value = "Yes"
 Cells(Target.Row, 5).Value = "No"

I'm getting syntax error.

Hi Lifeseeker

This is the entire code in your workbook

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column > 35 Then Exit Sub
    Application.EnableEvents = False
    If Cells(Target.Row, 2).Value >= 4 Then
        If DateDiff("n", Cells(Target.Row, 1).Value, Cells(Target.Row, 3).Value) <= 30 And DateDiff("h", Cells(Target.Row, 3).Value, Cells(Target.Row, 4).Value) >= 2 And DateDiff("h", Cells(Target.Row, 3).Value, Cells(Target.Row, 4).Value) <= 4, "Yes","No")
            then
            Cells(Target.Row, 5).Value = "Yes"
            Cells(Target.Row, 5).Value = "No"
        End If
        Application.EnableEvents = True

As you can see...there's a bit missing. There's an

End If

missing and an

End Sub

missing...not to mention...there's a reference to an "n" that's not defined. There's no way to trouble shoot this...not enough information...or code.

Lifeseeker,

Attached is a modified version of your example workbook. Cell C2 didn't have an actual date because the "pm" was immediately next to the "12:30". VBA doesn't care, but formulas do. So here's VBA code for a Worksheet_Change event to perform as requested, and it will convert all dates entered incorrectly (like "7/4/11 12:30pm") to correct dates ("7/4/11 12:30:00 PM") and then check if E should be Yes or No in accordance to your logic stated above:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngChg As Range: Set rngChg = Intersect(Me.Columns("A:D"), Target)
    If rngChg Is Nothing Then Exit Sub
    
    Dim DateCell As Range
    Application.EnableEvents = False
    
    For Each DateCell In Intersect(rngChg.EntireRow, Union(Me.Columns("A"), Me.Columns("C:D")))
        If IsDate(DateCell.Value) Then DateCell.Value = CDate(DateCell.Value)
    Next DateCell
    
    With Intersect(rngChg.EntireRow, Me.Columns("E"))
        .Formula = "=IF(AND(B" & .Row & ">=4,C" & .Row & "-A" & .Row & "<=30,AND(HOUR(D" & .Row & "-C" & .Row & ")>=2,HOUR(D" & .Row & "-C" & .Row & ")<=4)),""Yes"",""No"")"
        .Value = .Value
    End With
    
    Application.EnableEvents = True
    
End Sub