Forum Discussion
Solved
Checking if a time range falls between another time range
Good morning, I'm looking for a formula to help me check if a time range falls between another time range, please see example table below: A B C D E F G Date Time From Time To ...
AaronL89 I'll throw an option in. Sorry it isn't 'pretty'. Also it uses LET() so if you don't have Excel 365 I will have to modify it for the older formulas:
=LET(a,B1,b,C1,c,E1,d,F1,bb,b+(b<a),dd,d+(d<c),AND(c<bb,dd>a))EDIT:
actually the conversion is pretty simple:
=AND(E1<(C1+(C1<B1)),(F1+(F1<E1))>B1)
How about this?
=AND(IF(E2>0.5,E2-1,E2)>=IF(B2>0.5,B2-1,B2),IF(F2>0.5,F2-1,F2)<=IF(C2>0.5,C2-1,C2))
Corrected the previous error, now encountering the following:
11/09/2021 7:00 14:00 Sunny 8:55 10:15 FALSE Try the following formula instead and see if that works for all the scenarios...
=AND(OR(MOD(E2,1)>B2,MOD(E2,1)<C2),OR(MOD(F2,1)>B2,MOD(F2,1)<C2))Thank you for your help.
Ran the new formula, but getting false positives again? I've highlighted them in red below.
Also attached a sample set of data.
9/09/2021 19:00 1:15 Harry 8:00 8:40 FALSE 9/09/2021 19:00 1:15 Harriett 13:50 15:30 FALSE 10/09/2021 20:00 0:30 Tony 10:20 11:00 FALSE 11/09/2021 19:00 0:00 Garry 11:00 12:00 FALSE 11/09/2021 18:00 23:00 Gagan 7:00 8:00 TRUE 11/09/2021 7:00 14:00 Sunny 8:55 10:15 TRUE 12/09/2021 20:00 0:00 Mick 10:30 10:40 FALSE 13/09/2021 19:00 0:00 N/A FALSE 14/09/2021 9:30 19:00 Nicky 7:40 8:20 TRUE 14/09/2021 11:30 20:00 Ethan 21:05 21:30 TRUE 14/09/2021 11:30 20:00 N/A TRUE 14/09/2021 12:52 21:06 Ethan 21:05 21:30 TRUE 15/09/2021 9:30 16:00 SAWINDER 18:00 19:00 TRUE 15/09/2021 19:00 0:00 BALRAJ 13:25 14:20 FALSE 15/09/2021 7:00 12:00 BALRAJ 13:25 14:20 TRUE 15/09/2021 11:30 19:00 SUKHDEV 16:35 17:25 TRUE 15/09/2021 12:54 21:09 SUKHDEV 16:35 17:25 TRUE 16/09/2021 7:30 16:00 RODGER 15:25 15:45 TRUE 16/09/2021 15:30 20:30 SUKHDEV 8:55 10:25 TRUE 17/09/2021 7:00 12:00 John 13:00 13:50 TRUE 17/09/2021 8:00 16:00 John 13:00 13:50 TRUE 18/09/2021 10:00 19:00 SUKHDEV 7:10 8:10 TRUE 19/09/2021 9:00 17:00 RAVINDER 9:30 10:00 TRUE