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)
Still giving false positives with the time ranges provided.
I think the biggest issue with the times is that they are a range, but also could be over 2 days.
I think the biggest issue with the times is that they are a range, but also could be over 2 days.
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)
- I do have 365, and it looks like the first formula is a winner, thanks very much.
I'm after function so, looks don't matter in this contest. Appreciate your help.