Sep 20 2021 06:00 PM
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
Sep 20 2021 07:28 PM
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))
Sep 20 2021 07:40 PM
Sep 20 2021 08:23 PM
Corrected the previous error, now encountering the following:
11/09/2021 | 7:00 | 14:00 | Sunny | 8:55 | 10:15 | FALSE |
Sep 20 2021 09:14 PM
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))
Sep 21 2021 01:29 PM - edited Sep 21 2021 01:30 PM
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 |
Sep 21 2021 06:16 PM
Time formulas are really tricky. Okay, how about this in G1?
=AND(MOD(E1,1)>=B1,MOD(F1,1)<=C1)
Sep 22 2021 12:57 PM
Sep 22 2021 01:22 PM - edited Sep 22 2021 01:35 PM
Solution@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)
Sep 22 2021 06:05 PM
Sep 22 2021 01:22 PM - edited Sep 22 2021 01:35 PM
Solution@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)