Maggie Barr
Board Regular
- Joined
- Jan 28, 2014
- Messages
- 188
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
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 |