Cell G7 should show the time in HH : MM : SS until the US stock market is next open.
Cell G4 calculates either OPEN or CLOSED depending on if the VLOOKUP that searches the sheet ALGORITHM for either FALSE of TRUE for if each day the market is open (weekends are marked as FALSE as are any holidays) AND if the current time (found within cell B10)falls within market hours which are displayed at K6 and M6.
Current formulas:
G7 : =IF(G4="OPEN",M6-B10,K6-B10)
G4 : =IF(AND(VLOOKUP(B4,ALGORITHM!D5:ALGORITHM!J369,5,),B10>=K$6,B10<M$6),"OPEN","CLOSED")
Could anyone suggest a potential formula or VBA solution for this?
CodePudding user response：
Life is easier when you are only trying to match on one condition. The way you have structured the problem, your HOME lookup formula has to look at a time from the current sheet, and the date from the algorithm sheet. This makes each comparison complicated. It's easier when you can look in ONE cell or ONE range, and test ONE condition, to get a result. Sometimes, doing this involves creating a "helper column." The problem being there are use cases where helper columns aren't possible. But in your case the algorithm sheet is "non-aesthetic" and can support helper columns.
So, add two "helper" columns to the Algorithm table, so your table looks like this:
| 1 | A | B | C | D | E | (My Notes) |
|---|---|---|---|---|---|---|
| 2 | Date | Market Open | Remarks | untilOpen | untilClose | |
| 3 | 9/24/2022 | FALSE | - | - | - | Blank because Past |
| 4 | 9/25/2022 | FALSE | - | - | - | Blank because Past |
| 5 | 9/26/2022 | TRUE | - | - | - | Blank because Past |
| 6 | 9/27/2022 | TRUE | - | - | - | Blank because Past |
| 7 | 9/28/2022 | TRUE | - | - | - | Blank because Past |
| 8 | 9/29/2022 | TRUE | - | 0.44085 | 0.71168 | Next Opening/closing |
| 9 | 9/30/2022 | TRUE | - | 1.44085 | 1.71168 | |
| 10 | 10/1/2022 | FALSE | - | - | - | |
| 11 | 10/2/2022 | FALSE | - | - | - | |
| 12 | 10/3/2022 | TRUE | - | 4.44085 | 4.71168 | |
| 13 | 10/4/2022 | TRUE | - | 5.44085 | 5.71168 | |
| 14 | 10/5/2022 | TRUE | - | 6.44085 | 6.71168 | |
| 15 | 10/6/2022 | TRUE | - | 7.44085 | 7.71168 | |
| 16 | 10/7/2022 | TRUE | - | 8.44085 | 8.71168 | |
| 17 | 10/8/2022 | FALSE | - | - | - | |
| 18 | 10/9/2022 | FALSE | - | - | - | |
| 19 | 10/10/2022 | FALSE | Holiday | - | - | Blank because holiday |
| 20 | 10/11/2022 | TRUE | - | 12.44085 | 12.71168 | |
| 21 | 10/12/2022 | TRUE | - | 13.44085 | 13.71168 |
My column D is the date/time stamp of how soon until the next open. E is how soon until the next close. These are dynamic and update based on NOW each time the sheet recalculates. (In Excel the integer portion is days and the decimal portion is partial days, that can still be formatted "d hh:mm:ss".) I leave it to you to play with the Excel formats to get the appearance you want.
My table's time stamps are based on the night of 9/28, and I also threw in a fake holiday on 10/10/22 for illustration.
Here's the formulas for those cells, which would then be copied down each column to all rows:
B2 I changed to a simpler formula than yours to determine weekday/holiday (and copied it down):
=AND(WEEKDAY(A2,2)<=5,C2="-")
- B2 is TRUE if it's Mon through Fri and remarks is "-".
D2
=LET( open, A2 TIME(9,30,0) - NOW(), IF( AND( B2, open > 0), open, "-"))
- This says make the variable "open" = this row's date plus 9:30am minus NOW, which means this is an excel timestamp of time until that row's opening time.
- If this time is still in the future, AND this is an open day, then place this timestamp in the cell, otherwise make it "=".
E2
=LET( close, A2 TIME(16,0,0) - NOW(), IF( AND( B2, close > 0), close, "-"))
- Does the same, but for this row's closing time.
See what I did? The bulk of the logic is in the algorithm table. Because I blank the timestamp for days past and holidays, my lookup is literally this simple:
The days/hours/minutes/seconds to the next opening is simply
=MIN(D2:D21)The same for the next closing, which is
=MIN(E2:E21)
The ONLY remaining logic is for your home page to determine if the market is currently open or closed.
In fact, the next opening/closing event from "now" is simply =MIN(D2:E21) because the opening time will go blank in each row the moment the opening time passes. In other words the hours and minutes on your HOME page can always just come from =MIN(D2:E21) and all you have to to is determine if the current state is closed.
We've now removed all the complexity from the lookup. It's important to decide where you want to place the logic, in a complex lookup is often not the best place. We moved the logic to the schedule table.
BTW, some recommendations about your example:
In Excel, it's highly, highly recommended to avoid the use of merged cells. Your use of merged cells adds no additional aesthetic or usability. Merged cells decrease the intuitiveness of your logic and formulas, can complicate formatting, etc. Only use merged cells when there's a specific benefit to merged cells that can't be accomplished another way. You can just set row heights and column widths for better results.
Your data is not normalized for lookup and calculation. If the market opens and closes the same on any weekday that it's open, then just have a Mon - Fri row with 9:30 and 4:00pm in it. If you want the ability to support different hours each weekday, then have 10 cells (5 rows by 2 columns) with the open and close times, and then have the algorithm table do a lookup. You've created an aesthetic appearance of some day-of-week logic going on, and yet it catches the experienced person's eye that there actually isn't, and the time table is "just for show."