Forum Discussion
Checking if a time range is between a time range for same day but time starts or ends another day
Hello,
I am looking for a formula to check if a time range (appointment) is between a certain time range (work shift), but the complexity is both ranges can start on the previous day or end on the next day.
I have looked at both posts on time ranges here and solution did not work for me for times before or after 0h to24h.
Here are examples :
| Workshift | Workshift | Appointment | Appointment |
| start time | end time | start time | end time |
| 07:00 | 16:00 | 08:00 | 10:00 |
| 07:00 | 16:00 | 06:00 | 08:00 |
| 07:00 | 16:00 | 10:00 | 19:00 |
| 18:00 | 01:30 | 10:30 | 12:45 |
| 18:00 | 01:30 | 15:00 | 00:30 |
| 18:00 | 01:30 | 00:00 | 01:00 |
| 18:00 | 01:30 | 22:00 | 01:00 |
| 22:30 | 06:00 | 22:00 | 22:45 |
| 22:30 | 06:00 | 04:00 | 07:00 |
| 15:00 | 00:00 | 18:00 | 19:00 |
| 15:45 | 00:00 | 16:00 | 16:01 |
Any help would be greatly appreciated. I have spent at least 2 days on this. Thanks
1 Reply
I'd add the date to the time values.
For example, take 22;30 to 06:00 would become 13-Nov-2023 22:30 to 14-Nov-2023 06:00.
It would then be simple to check that the appointment start time is equal to or after the workshift start time, and the appointment end time is equal to or before the workshift end time.
