Complex Stock Market IF AND function with time and date restriction

Hello,

My goal is for the date and time to be live if the market is open, but appear as 4:00 PM and the last day the market was open if it is closed.

Right now, it is set up in two cells independent of each other -- (1) date and (2) time

I am working on a formula to display the date for a sheet with stock quotes and such. I am actually using a google spreadsheet with googlefinance commands. Since these do not update before 9:30 AM or after 4:00 PM, I am trying to find a formula to display the date for the actual day the quote is from.

So the goal is to have an IF function that says if it is before 9:30 AM, please refer to yesterday
But also IF it is Sunday or Monday, refer to Friday

It would be nice if holidays could be excluded as well, but they are not necessary if it requires a reference for dates in a separate array of cells...

=IF(NOW()-TODAY())<(9.5/24), TODAY()-1, IF(AND(WEEKDAY(TODAY()))=1, (NOW()-TODAY())<(9.5/24)), TODAY()-2, IF(AND(WEEKDAY(TODAY()))=2, (NOW()-TODAY())<(9.5/24)), TODAY()-3

Above is what I have, but it gives #ERROR! and says parse error.

--

In a separate cell, I would like a time stamp.

=IF((NOW()-TODAY())>(16/24) & (NOW()-TODAY())<(9.5/24), 16/24, NOW()-TODAY())

The goal is for this cell to say to be 4:00 PM if it is not between 9:30 AM and 4:00 PM, but for it to read the actual time (NOW()-TODAY()) if the time is between 9:30 AM and 4:00 PM

This displays a number buy does not stop at 4:00 PM like I want it to.

I think I have the right idea, but there are some flaws in the formulas. Thank you for your help.

Best,
Matt

Hello Matt,

Try using WORKDAY function like this for the date

=WORKDAY(NOW()+14.5/24,-1)

by adding 14.5 hours to the current date/time that will put you in tomorrow if the time is currently 09:30 or later.....but still remain today if earlier, -1 then gives you the previous working day, which should work in all cases

You can add a holiday range to the WORKDAY function if required

for the time you need an OR function like this

=IF(OR(NOW()-TODAY()>16/24,NOW()-TODAY()<9.5/24),16/24,NOW()-TODAY())