SOLVED

Checking if a time range falls between another time range

Copper Contributor

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:

 

ABCDEFG
DateTime FromTime ToEmployeeTime FromTime ToPresent?
9/09/202119:00 01:15John8:009:00 
9/09/202119:00 01:15Mary13:5015:30 
10/09/202120:00 00:30David10:2011:00 

 

In this example, I'd like G to return True/False based on if E and F falls between B and C

10 Replies

@AaronL89 

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

 

Time Comparison.jpg

 

@Subodh_Tiwari_sktneer 

 

Seems to have a Value Error:

AaronL89_0-1632192028663.png

 

Is it the time formatting?

 

@Subodh_Tiwari_sktneer 

 

Corrected the previous error, now encountering the following:

 

11/09/20217:0014:00Sunny8:5510:15FALSE

 

AaronL89_0-1632194578024.png

 

@AaronL89 

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

 

Time Comparison.jpg

@Subodh_Tiwari_sktneer 

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/202119:001:15Harry8:008:40FALSE
9/09/202119:001:15Harriett13:5015:30FALSE
10/09/202120:000:30Tony10:2011:00FALSE
11/09/202119:000:00Garry11:0012:00FALSE
11/09/202118:0023:00Gagan7:008:00TRUE
11/09/20217:0014:00Sunny8:5510:15TRUE
12/09/202120:000:00Mick10:3010:40FALSE
13/09/202119:000:00N/A  FALSE
14/09/20219:3019:00Nicky7:408:20TRUE
14/09/202111:3020:00Ethan21:0521:30TRUE
14/09/202111:3020:00N/A  TRUE
14/09/202112:5221:06Ethan21:0521:30TRUE
15/09/20219:3016:00SAWINDER18:0019:00TRUE
15/09/202119:000:00BALRAJ13:2514:20FALSE
15/09/20217:0012:00BALRAJ13:2514:20TRUE
15/09/202111:3019:00SUKHDEV16:3517:25TRUE
15/09/202112:5421:09SUKHDEV16:3517:25TRUE
16/09/20217:3016:00RODGER15:2515:45TRUE
16/09/202115:3020:30SUKHDEV8:5510:25TRUE
17/09/20217:0012:00John13:0013:50TRUE
17/09/20218:0016:00John13:0013:50TRUE
18/09/202110:0019:00SUKHDEV7:108:10TRUE
19/09/20219:0017:00RAVINDER9:3010:00TRUE

 

AaronL89_0-1632255175930.png

 

@AaronL89 

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.
best response confirmed by AaronL89 (Copper Contributor)
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)

 

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.
1 best response

Accepted Solutions
best response confirmed by AaronL89 (Copper Contributor)
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)

 

View solution in original post