How to find gap length between date/time?

[jm5498]

Hello, I have the following spreadsheet:

I'm trying to figure out a formula that finds a gap when no events are valid. So in this case, there are no events valid from 6:00a - 6:03a. I want a way where excel tells me that through a formula in addition to any other gaps that there may be. Thank you!

 

[Mackeral]

I'm not sure exactly what you mean. Row 3 is in Row 1 is what I think you mean.

 

[Mackeral]

I got this

Start	End	Row
5/1/2022 5:01	5/1/2022 5:30	FALSE
5/1/2022 5:06	5/1/2022 5:30	TRUE
5/1/2022 6:03	5/1/2022 6:15	FALSE
5/1/2022 6:06	5/1/2022 6:30	FALSE

With this:

Public Const _
    ColStart = 1, _
    ColEnd = 2, _
    ColTest = 3

Function Time_Test(Calling_Row)
    Sheets("Test").Activate
   
    Time_Test = False
    Start_Date = Cells(Calling_Row, ColStart)
    End_Date = Cells(Calling_Row, ColEnd)
   
    For Row = 11 To 14
        If Calling_Row <> Row Then
            T1 = Start_Date >= Cells(Row, ColStart)
            T2 = (End_Date <= Cells(Row, ColEnd))
            If T1 And T2 Then
                Time_Test = True
                Exit Function
            End If
        End If
    Next Row
   
End Function

How would this be different to be of use?