If I'm understanding the example, you have:
Date and Time values in column A, formatted to show Date only.
Date and Time values in column B, formatted to show Time only.
Number values in column C.
Number values in column D.
Column E is empty.
F2 to display the date part of the earliest Time and Date value in column A
F3 to display a date one day later than the date displayed in F2
G2 to display the 8:35 AM Open value for the date shown in F2
G3 to display the 8:35 AM Open value for the date shown in F3
H2 to display the 9:00 AM Close value for the date shown in F2
H3 to display the 9:00 AM Close value for the date shown in F3
Time and Date value in column A
Here's one way to go about it.
Column A contains Date and Time values, starting with the D&T shown in A2, then incrementing by five minutes until 9:00 AM is reached in row 7. Enter the formula below in A3, and fill it down to A7.
A8 is one day later than A2:
Set A8 (and A2) to Bold to mark the start of a new day.
Set the cell format for these cells to Date & Time, Date formatted as you choose, Time set to None. (alternately, you can display the full Date and Time value in this column and eliminate column B)
Now select cells A3 to A8, then drag the Fill control down to fill the rest of the column using the same set of formulas (and the Bold setting for the first of each new day).
Columns C and D contain entered data. Rather than meticulously copy your sample data, I've used O and strings of os to marl the Open cells to be returned, and C (cc) to mark the Close cells.
F2, G2 and H2 contain variations of the same OFFSET formula, filled down (to the row showing error triangles):
Select all three and Fill down.
The error triangles are a 'bad reference error' caused by the Row offset ( ROW()-2)*6+1 ) pointing to a row not on the table.