Time Formula Help

[catherine9910]

I have a table that has a macro to pull data from a source. I will run it at the beginning and end of my shift. I have a separate table that I record certain data from the macro table to tell me what we started with and finished with. Is there a formula that will allow me to pull data from the same source at two different time of day without erasing the start of day data?(Below is an example of what I am wanting) 

[#2]

@catherine9910 

I would recommend extending the functionality of your macros rather than using formulas for this purpose.  There are techniques using circular referencing to prevent a cell value from updating but they are something of a last resort and could damage the performance of your workbook.

[#3]

@Peter Bartholomew Ok I will try that. I just wasn't sure if there was a formula that I could use saying copy this cell if between this and this time. Like =B3 from 9:00am to 10:00am

[#4]

@catherine9910 

Yes, you can certainly do that.  The catch is what happens outside the time interval.  If you do not specify a value, the cell will simply show FALSE, which would be unhelpful.  The answer is to return the cell's existing value.  That creates a circular reference and you will need to change the calculation options to permit iterative calculation.

[#5]

Do you know of a formula that would work? I was trying..
=IF(AND(K2,"<10:00PM",">11:00PM"),L5)
I was hoping that if it was in between 10 and 11 that it would capture the value in that cell.

[#6]

@catherine9910 

The first problem is that "10:00PM" will not be recognised as a time.  Time in Excel is a measured as a fraction of a day so 11:00PM is =23/24=0.9583333.  The logical part of the formula 

=IF(AND(K2,"<10:00PM",">11:00PM"),L5)

would work if expressed in the form

= IF(AND(K2>22/24, K2<23/24), L5)

The catch is that as soon as the time goes past 11:00PM the result returns to FALSE.  To avoid that, the formula should read and return its own value

= IF(AND(K2>22/24, K2<23/24), L5, M5),

hence the circular reference.