Issue with IF statements using time

MrAnalyst22

New Member
Joined
Jun 25, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
TimeBefore/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:

TimeBefore/After 8:30amBefore
10:20AFTER OPENAFTER CLOSE

Any help is much appreciated!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
TimeBefore/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:

TimeBefore/After 8:30amBefore/After 3:00pm
10:20AFTER OPENAFTER CLOSE

Any help is much appreciated!
Corrected the titling of the 2nd table, sorry about that.
 
Upvote 0
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
KLMN
1TimeBefore/After 8:30amBefore/After 3:00pmMarket Status
211:46AFTER OPENBEFORE CLOSECLOSED
Market Time
Cell Formulas
RangeFormula
K2K2=MOD(NOW(),1)
L2L2=IF(K2>=TIMEVALUE("08:30"),"AFTER OPEN","BEFORE OPEN")
M2M2=IF(K2<=TIMEVALUE("15:30"),"BEFORE CLOSE","AFTER CLOSE")
N2N2=IF(AND(WEEKDAY(TODAY(),3)<5,K2>=TIMEVALUE("08:30"),K2<=TIMEVALUE("15:30")),"OPEN","CLOSED")
 
Upvote 0
Solution
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
KLM
1
209:44AFTER OPENBEFORE CLOSE
3
14a
Cell Formulas
RangeFormula
K2K2=NOW()
L2L2=IF(MOD(K2,1)>=TIMEVALUE("08:30"),"AFTER OPEN","BEFORE OPEN")
M2M2=IF(MOD(K2,1)<=TIMEVALUE("15:30"),"BEFORE CLOSE","AFTER CLOSE")
 
Upvote 0
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!
 
Upvote 0
Date and Time 2021.xlsm
KLM
210:09AFTER OPENBEFORE CLOSE
3
4Mon 27-Jun-22
510:00:00
6Mon 27-Jun-22 10:00 AMOpen
7
14a
Cell Formulas
RangeFormula
K2K2=NOW()
L2L2=IF(MOD(K2,1)>=TIMEVALUE("08:30"),"AFTER OPEN","BEFORE OPEN")
M2M2=IF(MOD(K2,1)<=TIMEVALUE("15:30"),"BEFORE CLOSE","AFTER CLOSE")
K6K6=K4+K5
L6L6=IF(AND(WEEKDAY(K6,2)<6,MOD(K6,1)>=0.3542,MOD(K6,1)<0.6458),"Open","Closed")
 
Upvote 0
Date and Time 2021.xlsm
IJKL
1Holidays
201-01-22
314-02-22
427-06-22Mon 27-Jun-22
510:00:00
627-Jun-22as a holidayMon 27-Jun-22 10:00 AMClosed
7Tue 28-Jun-22 10:00 AMOpen
8Wed 29-Jun-22 8:00 AMClosed
9Wed 29-Jun-22 4:00 PMClosed
10
14a
Cell Formulas
RangeFormula
K6K6=K4+K5
L6:L9L6=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
NameRefers ToCells
'14a'!Holidays='14a'!$I$2:$I$4L6:L9
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top