Issue with IF statements using time

[MrAnalyst22]

I am trying to create an indicator to show if the financial markets are currently open or closed based on current time, and running into some issues. For anyone unfamiliar, market hours are 8:30am-3:00pm Central Time. I have to build in a factor for day of the week as well, but I am trying to get the time portion correct first. I tried first using an IF(AND( statement, but kept getting an incorrect result, so for now I've broken it down into two simple IF statements with the intended result being "before or after 8:30am" and "before or after 3:00pm". The 8:30 indicator seems to work, but as of writing this, it is 10:20am and the "before 3:00pm indicator" is showing that it is in fact after 3:00pm. FYI, K2 is used in the formulas as that's where my time value is located in my sheet. Apologies for not using the mini sheet feature as I'm using my work computer with heavy cybersecurity that restricts my downloads.

Here's what I have

Time	Before/After 8:30am	Before/After 3:00pm
=now() (formatted as 24 hour clock with just hours and minutes - i.e., 13:30)	=IF(K2>=TIMEVALUE("08:30"),"AFTER OPEN","BEFORE OPEN")	=IF(K2<=TIMEVALUE("15:30"),"BEFORE CLOSE","AFTER CLOSE")

Results, as of 10:20am:

Time	Before/After 8:30am	Before
10:20	AFTER OPEN	AFTER CLOSE

Any help is much appreciated!

 

[MrAnalyst22]

I am trying to create an indicator to show if the financial markets are currently open or closed based on current time, and running into some issues. For anyone unfamiliar, market hours are 8:30am-3:00pm Central Time. I have to build in a factor for day of the week as well, but I am trying to get the time portion correct first. I tried first using an IF(AND( statement, but kept getting an incorrect result, so for now I've broken it down into two simple IF statements with the intended result being "before or after 8:30am" and "before or after 3:00pm". The 8:30 indicator seems to work, but as of writing this, it is 10:20am and the "before 3:00pm indicator" is showing that it is in fact after 3:00pm. FYI, K2 is used in the formulas as that's where my time value is located in my sheet. Apologies for not using the mini sheet feature as I'm using my work computer with heavy cybersecurity that restricts my downloads.

Here's what I have

Time	Before/After 8:30am	Before/After 3:00pm
=now() (formatted as 24 hour clock with just hours and minutes - i.e., 13:30)	=IF(K2>=TIMEVALUE("08:30"),"AFTER OPEN","BEFORE OPEN")	=IF(K2<=TIMEVALUE("15:30"),"BEFORE CLOSE","AFTER CLOSE")

Results, as of 10:20am:

Time	Before/After 8:30am	Before/After 3:00pm
10:20	AFTER OPEN	AFTER CLOSE

Any help is much appreciated!

Corrected the titling of the 2nd table, sorry about that.

 

[6StringJazzer]

NOW() includes the date. Use =MOD(NOW(),1) to isolate the time.

Also you can combine the two with AND. And I included a test for weekday, but this won't recognize holidays. If you want to include holidays you need a list of holidays, and include that in the formula.

$scratch.xlsm
	K	L	M	N
1	Time	Before/After 8:30am	Before/After 3:00pm	Market Status
2	11:46	AFTER OPEN	BEFORE CLOSE	CLOSED
Market Time

Cell Formulas
Range	Formula
K2	K2	=MOD(NOW(),1)
L2	L2	=IF(K2>=TIMEVALUE("08:30"),"AFTER OPEN","BEFORE OPEN")
M2	M2	=IF(K2<=TIMEVALUE("15:30"),"BEFORE CLOSE","AFTER CLOSE")
N2	N2	=IF(AND(WEEKDAY(TODAY(),3)<5,K2>=TIMEVALUE("08:30"),K2<=TIMEVALUE("15:30")),"OPEN","CLOSED")

 

[Dave Patton]

The formatting does not change the value in the cell K2
Review the suggestion and your formula with Formulas Evaluate Formula

Date and Time 2021.xlsm
	K	L	M
1
2	09:44	AFTER OPEN	BEFORE CLOSE
3
14a

Cell Formulas
Range	Formula
K2	K2	=NOW()
L2	L2	=IF(MOD(K2,1)>=TIMEVALUE("08:30"),"AFTER OPEN","BEFORE OPEN")
M2	M2	=IF(MOD(K2,1)<=TIMEVALUE("15:30"),"BEFORE CLOSE","AFTER CLOSE")

 

[MrAnalyst22]

I figured it had something to do with the time. the MOD formula worked - thank you!! I had no idea that the WEEKDAY function existed, that will greatly simply my final formula.

You are both awesome!

 

[Dave Patton]

Date and Time 2021.xlsm
	K	L	M
2	10:09	AFTER OPEN	BEFORE CLOSE
3
4	Mon 27-Jun-22
5	10:00:00
6	Mon 27-Jun-22 10:00 AM	Open
7
14a

Cell Formulas
Range	Formula
K2	K2	=NOW()
L2	L2	=IF(MOD(K2,1)>=TIMEVALUE("08:30"),"AFTER OPEN","BEFORE OPEN")
M2	M2	=IF(MOD(K2,1)<=TIMEVALUE("15:30"),"BEFORE CLOSE","AFTER CLOSE")
K6	K6	=K4+K5
L6	L6	=IF(AND(WEEKDAY(K6,2)<6,MOD(K6,1)>=0.3542,MOD(K6,1)<0.6458),"Open","Closed")

 

[Dave Patton]

Date and Time 2021.xlsm
	I	J	K	L
1	Holidays
2	01-01-22
3	14-02-22
4	27-06-22		Mon 27-Jun-22
5			10:00:00
6	27-Jun-22	as a holiday	Mon 27-Jun-22 10:00 AM	Closed
7			Tue 28-Jun-22 10:00 AM	Open
8			Wed 29-Jun-22 8:00 AM	Closed
9			Wed 29-Jun-22 4:00 PM	Closed
10
14a

Cell Formulas
Range	Formula
K6	K6	=K4+K5
L6:L9	L6	=IF(AND(WEEKDAY(K6,2)<6,MOD(K6,1)>=0.3542,MOD(K6,1)<0.6458,ISNA(MATCH(INT(K6),Holidays,0))),"Open","Closed")

Named Ranges
Name	Refers To	Cells
'14a'!Holidays	='14a'!$I$2:$I$4	L6:L9