Excel test to see if time falls between two times spanning midnight.

[Maggie Barr]

Greetings, and thank you in advance if you can help.
I am on a PC using Microsoft Office Pro 2019 desktop.
I need to test if a time falls between two times that span midnight. It is for testing if protocol was followed for surveys. So the time survey conducted should be between the evening of one day and pre sunrise the next. I have a table of times (see below), and I need to test the survey time in one row by the start and end times in the same row. I have tried +IF(AND formulas, but it just doesn't work, and everything I find online is for set start and end times, not cell referenced, and they do not pan midnight. I even tried using the time values (HA), and of course that didn't work. I have almost 5,000 of these to check, possibly more, and it will continue next year, so it would be great to be able to automate this in some way.
Any guidance would be greatly appreciated, and thank you in advance if you can help.
Best Wishes,
Maggie

Survey time	Start	End
21:27​	20:25	04:47
21:37​	20:25	04:47
21:47​	20:25	04:47
21:57​	20:25	04:47
22:07​	20:25	04:47
22:17​	20:25	04:47
22:27​	20:25	04:47
22:37​	20:25	04:47
22:47​	20:25	04:47
22:57​	20:25	04:47
21:33​	20:34	04:41
21:43​	20:34	04:41
21:53​	20:34	04:41
22:03​	20:34	04:41
22:13​	20:34	04:41
22:23​	20:34	04:41
22:33​	20:34	04:41
22:43​	20:34	04:41
22:53​	20:34	04:41
23:03​	20:34	04:41
21:27​	20:28	04:44
21:37​	20:28	04:44
21:47​	20:28	04:44
21:57​	20:28	04:44
22:07​	20:28	04:44
22:17​	20:28	04:44
22:27​	20:28	04:44
22:37​	20:28	04:44

 

[jtakw]

Hi,

You didn't explain what or how you want your results.
Assuming the Time values in the 3 Columns do Not include Date values, may be this:

Book3.xlsx
	A	B	C	D
1	Survey time	Start	End	Compliance
2	21:27	20:25	4:47	TRUE
3	22:27	20:25	4:47	TRUE
4	23:27	20:25	4:47	TRUE
5	22:07	20:25	4:47	TRUE
6	23:07	20:25	4:47	TRUE
7	21:27	20:25	4:47	TRUE
8	22:27	20:25	4:47	TRUE
9	23:27	20:25	4:47	TRUE
10	22:07	20:25	4:47	TRUE
11	23:07	20:25	4:47	TRUE
12	21:27	20:34	4:41	TRUE
13	22:27	20:34	4:41	TRUE
14	23:27	20:34	4:41	TRUE
15	22:07	20:34	4:41	TRUE
16	23:07	20:34	4:41	TRUE
17	21:27	20:34	4:41	TRUE
18	22:27	20:34	4:41	TRUE
19	23:27	20:34	4:41	TRUE
20	22:07	20:34	4:41	TRUE
21	23:07	20:34	4:41	TRUE
22	17:07	20:28	4:44	FALSE
23	18:27	20:28	4:44	FALSE
24	19:27	20:28	4:44	FALSE
25	20:27	20:28	4:44	FALSE
26	21:27	20:28	4:44	TRUE
27	22:27	20:28	4:44	TRUE
28	23:27	20:28	4:44	TRUE
29	22:27	20:28	4:44	TRUE
Sheet1046

Cell Formulas
Range	Formula
D2:D29	D2	=AND(A2>B2,A2<C2+24)

 

[steve the fish]

Maybe this:

=OR(A2>=B2,A2<=C2)

 

[Maggie Barr]

jtakw,
Thank you for reaching out and providing information. I thought at firs it was working, but when I went through the data to search for FLASE results, any Survey time after midnight had FALSE despite that it was still less than the end protocol time. Survey time of 00:13 and Protocol of 20:41 start and 04:44 end came back as false.
Any further advice?
Thank you again for your efforts!
Maggie

 

[jtakw]

I think @steve the fish formula above should work ?

 

[Maggie Barr]

Maybe this:

=OR(A2>=B2,A2<=C2)

steve the fish,
Thank you for reaching out! I thought for sure that it had to be an AND in order to function properly because I need it to be between the two times, but I ran your formula and it appears to be working. I can't wrap my brain around why, but it must be that the OR is acceptable because it can be viewed as two separate tests, greater than or equal to the start time to midnight, OR less than or equal to the end time, and since the time spans across midnight, it actually functions fine. I wonder if the time span was within a single day if it would still work. I'll have to play around and find out just so I know.
Either way, it appears to be working, and I will modify it to a IF(OR so I can customize the text in response to the test and add the <= and >=.
Thank you so much! Love the name by the way!
Best Wishes,
Maggie

 

[Maggie Barr]

jtakw,
Just an update that the OR formula that steve the fish provided worked. I thought it had to be an AND formula, but apparently not.
I wanted to thank you for your efforts though, I appreciate it.
Maggie

 

[jtakw]

jtakw,
Just an update that the OR formula that steve the fish provided worked. I thought it had to be an AND formula, but apparently not.
I wanted to thank you for your efforts though, I appreciate it.
Maggie

You're welcome.

I Pointed that out in my Post #5 above.