If Statement

[vharp91]

Can someone help me with a formula that will have the value "Yes" in column T if the time in column I is between 7:00 & 19:00 (time is military). Thank you!

 

[Joe4]

Place this formula in cell T3 and copy down for all rows:

=IF(AND(MOD(I3,1)>=(7/24),MOD(I3,1)<=(19/24)),"Yes","")

 

[etaf]

=IF( AND( I2-INT(I2) >= TIMEVALUE("07:00:00"), I2-INT(I2) <= TIMEVALUE("19:00:00")),"Yes","")

Book1
	I	J	K	L	M	N	O	P	Q	R	S	T
1
2	1/1/24 12:30						7:00 & 19:00 (time is military). Thank you!					Yes
3	1/2/24 13:10											Yes
4	1/3/24 13:50											Yes
5	1/4/24 14:30											Yes
6	1/5/24 15:10											Yes
7	1/6/24 15:50											Yes
8	1/7/24 16:30											Yes
9	1/8/24 17:10											Yes
10	1/9/24 17:50											Yes
11	1/10/24 18:30											Yes
12	1/11/24 19:10
13	1/12/24 19:50
14	1/13/24 20:30
15	1/14/24 21:10
16	1/15/24 21:50
17	1/16/24 22:30
18	1/17/24 23:10
19	1/18/24 23:50
20	1/20/24 0:30
21	1/21/24 1:10
22	1/22/24 1:50
23	1/23/24 2:30
24	1/24/24 3:10
25	1/25/24 3:50
26	1/26/24 4:30
27	1/27/24 5:10
28	1/28/24 5:50
29	1/29/24 6:30
30	1/30/24 7:10											Yes
31	1/31/24 7:50											Yes
32	2/1/24 8:30											Yes
33	2/2/24 9:10											Yes
34	2/3/24 9:50											Yes
35	2/4/24 10:30											Yes
36
Sheet1

Cell Formulas
Range	Formula
T2:T35	T2	=IF( AND( I2-INT(I2) >= TIMEVALUE("07:00:00"), I2-INT(I2) <= TIMEVALUE("19:00:00")),"Yes","")

 

[vharp91]

I realized this is not working for what I am intending and I believe it is because I worded the question incorrectly. I need the formula to pull in the value "Yes" in column U if the time in column I is between 7:00 PM & 7:00 AM? For Example, in the below data set, only I9 & I10 should pull in a "Yes" into U9 & U10.

 

[etaf]

well you could do a NOT() on my formula so you get the opposite
=IF( NOT(AND( I2-INT(I2) >= TIMEVALUE("07:00:00"), I2-INT(I2) <= TIMEVALUE("19:00:00"))),"Yes","")
if you dont want the 7am and 19:00 - remove the =

or use an OR () , using Joe4 formula
=IF(OR(MOD(I3,1)<(7/24),MOD(I3,1)>(19/24)),"Yes","")

assumign you dont want a yes for 7pm or 7am
otherwise add the =

also you want in column U - i left in T - just change the formula so its in column U , NOT T
or is this an additional requirement for U and the OLD post for T

Book3
	I	J	K	L	M	N	O	P	Q	R	S	T
1
2	1/1/24 12:30						7:00 & 19:00 (time is military). Thank you!
3	1/2/24 13:10
4	1/3/24 13:50
5	1/4/24 14:30
6	1/5/24 15:10
7	1/6/24 15:50
8	1/7/24 16:30
9	1/8/24 17:10
10	1/9/24 17:50
11	1/10/24 18:30
12	1/11/24 19:10											Yes
13	1/12/24 19:50											Yes
14	1/13/24 20:30											Yes
15	1/14/24 21:10											Yes
16	1/15/24 21:50											Yes
17	1/16/24 22:30											Yes
18	1/17/24 23:10											Yes
19	1/18/24 23:50											Yes
20	1/20/24 0:30											Yes
21	1/21/24 1:10											Yes
22	1/22/24 1:50											Yes
23	1/23/24 2:30											Yes
24	1/24/24 3:10											Yes
25	1/25/24 3:50											Yes
26	1/26/24 4:30											Yes
27	1/27/24 5:10											Yes
28	1/28/24 5:50											Yes
29	1/29/24 6:30											Yes
30	1/30/24 7:10
31	1/31/24 7:50
32	2/1/24 8:30
33	2/2/24 9:10
34	2/3/24 9:50
35	2/4/24 10:30
Sheet1

Cell Formulas
Range	Formula
T2:T35	T2	=IF( NOT(AND( I2-INT(I2) >= TIMEVALUE("07:00:00"), I2-INT(I2) <= TIMEVALUE("19:00:00"))),"Yes","")

 

[Joe4]

You just need to change one word and flip the < and > signs in my formula, i.e.

=IF(OR(MOD(I3,1)<=(7/24),MOD(I3,1)>=(19/24)),"Yes","")

 

[Joe4]

well you could do a NOT() on my formula so you get the opposite
=IF( NOT(AND( I2-INT(I2) >= TIMEVALUE("07:00:00"), I2-INT(I2) <= TIMEVALUE("19:00:00"))),"Yes","")

Yep, NOT changes AND to OR, and changes >= to <= and changes <= to >=

 

[MARK858]

@vharp91 is this not the same question you asked in the thread below?

Formula help - between two times

Can someone help me with a formula that will have the value "Yes" in column U if the time in column I is between 7:00 PM & 7:00 AM? For Example, in the below data set, only I9 & I10 should pull in a "Yes" into U9 & U10. Thank you

www.mrexcel.com