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)
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 |
Time formulas are really tricky. Okay, how about this in G1?
=AND(MOD(E1,1)>=B1,MOD(F1,1)<=C1)- 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.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.