logical test problems creating formula

xcelvogelsong

New Member
Joined
Aug 17, 2010
Messages
2
I have multiple time frames and am trying to create a logical test that will return true if the time given is within the two times. any ideas
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,212
Something like this gives you the idea of how to check whether a time is between an upper and lower time:
Excel Workbook
ABCD
1TimeIs Time Between Upper and LowerLower Time
22:00 AMFALSE4:15 AM
35:15 AMTRUEUpper Time
46:00 PMTRUE7:33 PM
52:33 PMTRUE
6
...
Cell Formulas
Range(s)Formula
B2=AND(A2>=$D$2,A2<=$D$4)
B3=AND(A3>=$D$2,A3<=$D$4)
B4=AND(A4>=$D$2,A4<=$D$4)
B5=AND(A5>=$D$2,A5<=$D$4)
 

xcelvogelsong

New Member
Joined
Aug 17, 2010
Messages
2
you are right, this formula does work for one time frame, I have multiple time frames that i need to find out if one given time fits into any of them
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,212
Maybe this:

Here is the data, assumptions and formulas (formulas in green cells):
Excel Workbook
ABCDEF
1TimeIs Time Between Upper and LowerLower TimeLower TimeLower Time
22:00 AMFALSE4:15 AM4:15 PM5:53 PM
35:15 AMTRUEUpper TimeUpper TimeUpper Time
46:00 PMTRUE7:33 AM5:33 PM7:33 PM
52:33 PMFALSE
6
...


Enter this formula in B2 and copy down:

=OR(AND(A2>=$D$2,A2<=$D$4),AND(A2>=$E$2,A2<=$E$4),AND(A2>=$F$2,A2<=$F$4))



Picture of formula:
Excel Workbook
B
2FALSE
...
Cell Formulas
Range(s)Formula
B2=OR(AND(A2>=$D$2,A2<=$D$4),AND(A2>=$E$2,A2<=$E$4),AND(A2>=$F$2,A2<=$F$4))
 

Forum statistics

Threads
1,082,557
Messages
5,366,304
Members
400,882
Latest member
zaldy_a

Some videos you may like

This Week's Hot Topics

Top