Time Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1947310%22%20slang%3D%22en-US%22%3ETime%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1947310%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20that%20has%20a%20macro%20to%20pull%20data%20from%20a%20source.%20I%20will%20run%20it%20at%20the%20beginning%20and%20end%20of%20my%20shift.%20I%20have%20a%20separate%20table%20that%20I%20record%20certain%20data%20from%20the%20macro%20table%20to%20tell%20me%20what%20we%20started%20with%20and%20finished%20with.%20Is%20there%20a%20formula%20that%20will%20allow%20me%20to%20pull%20data%20from%20the%20same%20source%20at%20two%20different%20time%20of%20day%20without%20erasing%20the%20start%20of%20day%20data%3F(Below%20is%20an%20example%20of%20what%20I%20am%20wanting)%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Help.PNG%22%20style%3D%22width%3A%20371px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237197i13826EF8B9D2D11C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Help.PNG%22%20alt%3D%22Help.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1947310%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1947379%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1947379%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F888027%22%20target%3D%22_blank%22%3E%40catherine9910%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20recommend%20extending%20the%20functionality%20of%20your%20macros%20rather%20than%20using%20formulas%20for%20this%20purpose.%26nbsp%3B%20There%20are%20techniques%20using%20circular%20referencing%20to%20prevent%20a%20cell%20value%20from%20updating%20but%20they%20are%20something%20of%20a%20last%20resort%20and%20could%20damage%20the%20performance%20of%20your%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1947393%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1947393%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3BOk%20I%20will%20try%20that.%20I%20just%20wasn't%20sure%20if%20there%20was%20a%20formula%20that%20I%20could%20use%20saying%20copy%20this%20cell%20if%20between%20this%20and%20this%20time.%20Like%20%3DB3%20from%209%3A00am%20to%2010%3A00am%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1948071%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1948071%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F888027%22%20target%3D%22_blank%22%3E%40catherine9910%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20you%20can%20certainly%20do%20that.%26nbsp%3B%20The%20catch%20is%20what%20happens%20outside%20the%20time%20interval.%26nbsp%3B%20If%20you%20do%20not%20specify%20a%20value%2C%20the%20cell%20will%20simply%20show%20FALSE%2C%20which%20would%20be%20unhelpful.%26nbsp%3B%20The%20answer%20is%20to%20return%20the%20cell's%20existing%20value.%26nbsp%3B%20That%20creates%20a%20circular%20reference%20and%20you%20will%20need%20to%20change%20the%20calculation%20options%20to%20permit%20iterative%20calculation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1949627%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1949627%22%20slang%3D%22en-US%22%3EDo%20you%20know%20of%20a%20formula%20that%20would%20work%3F%20I%20was%20trying..%3CBR%20%2F%3E%3DIF(AND(K2%2C%22%26lt%3B10%3A00PM%22%2C%22%26gt%3B11%3A00PM%22)%2CL5)%3CBR%20%2F%3EI%20was%20hoping%20that%20if%20it%20was%20in%20between%2010%20and%2011%20that%20it%20would%20capture%20the%20value%20in%20that%20cell.%3C%2FLINGO-BODY%3E
Occasional Contributor

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) Help.PNG

5 Replies

@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.

@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

@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.

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.

@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.