Forum Discussion
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))
