Calculate if any part of a time range is within another time range.

[crookesa]

Hi,

I have a time window for what is considered a night shift, e.g. 0000 - 0559. These times can change wither before or after midnight.
I want to supply a start and end time of a numbers of shifts and see if any part of those shifts falls within the hours above.

I had tried this formula

=IF(OR(IF(AND(G2>=$A$2,G2<=$B$2),TRUE,FALSE)=TRUE,IF(AND(H2>=$A$2,H2<=$B$2),TRUE,FALSE)=TRUE),TRUE,FALSE)

where A2 & B2 is night shift criteria start & end time and G2 & H2 are the respective start and end times of the shifts I want to check.

I can only get it to work out if a start or end time is within the window but not if any of the duty is included.

Looking for help with the logic thanks...

 

[crookesa]

Still trying to work on this and taking a new approach but still don't have it quite right

.
I thought I could check if the duty start time was between the criteria using the following in E5. The same logic is applied to duty end time. Then the "Any parts?" is either cell TRUE.

=OR(MOD(A5,1)>$E$2,MOD(A5,1)<$F$2)

 

[crookesa]

Just wondering if anyone has any help available that would let me find my a solution here