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 | Employee | Time From | Time To | Present? |
| 9/09/2021 | 19:00 | 01:15 | John | 8:00 | 9:00 | |
| 9/09/2021 | 19:00 | 01:15 | Mary | 13:50 | 15:30 | |
| 10/09/2021 | 20:00 | 00:30 | David | 10:20 | 11:00 |
In this example, I'd like G to return True/False based on if E and F falls between B and C
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
- c>e=true
