Comparing times in two tables

  • Thread starter Thread starter Hugh
  • Start date Start date
H

Hugh

I have two event logs which I am trying to compare. How can I establish if
an event in one log occurred around the same point in time of any event in
the other log, bearing in mind that the times of the events in each log may
not be exactly coincident, but might be 5 or 10 minutes apart. I’d only need
to return one comparison value to indicate the corresponding time frames in
the two logs, but still I’m really stumped. Any help would be much
appreciated.
 
=ABS(A2-B2)>TIME(0,10,0)
returns True if time difference is greater than 10 minutes. Adjust cell
references to your real layout!

Regards,
Stefi

„Hugh†ezt írta:
 
Suppose you have the times stored in Col A and Col B

=IF(B:B-A:A>MINUTE(10),"close",IF(B:B-A:A<MINUTE(10),"close",""))
 
Hi Stefi,
Thanks for your help but that’s not quite it. I’ve tried your suggestion in
combination with my previous attempts but without success. I’ll try and
better explain what I need.
Column A contains IDs for Log1, B contains times of events for Log1, C
contains IDs for Log2, D contains times of events for Log2.
I need to establish if the time in B2, for instance, occurs within 10
minutes of ANY event in D:D, and then supply a cell reference, or similar.
I’ve been trying to use MATCH, which, if the times of occurrence in the two
tables were exact, would work fine (I could then use the number returned with
OFFSET), but I can’t factor in the time variance between the tow lists. Can
you help?
Thanks,
Hugh
 
Hi Jacob,
Thanks for your efforts but that’s not quite what I want. Please see my
response to Stefi, above. I hope you can help.
Regards,
Hugh
 
Well, an exact question has a chance to receive an exact answer!
What about this one:
=MATCH(1,--(ABS($B$2-D2:D5)<TIME(0,10,0)),0)
It's an array formula, confirm with Ctrl+Shift+Enter!
It returns position of the first time in D2:D5 within 10 minutes of time in
B2. If you want row No of the hit, add 1 to the result (because I supposed a
header in row 1.

Stefi


„Hugh†ezt írta:
 
Thanks Stefi. With a little manipulation, I got there.
You've saved me a whole heap of time.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Time remaining until event 2
My Cost Changed? Need Help to find the changes Compare values form 1
Calculating Non-working Dates and Times 6
Lookup for cells with text in them 4
Converting date/time serial values to cumulative time totals... 4
Button macro to move/delete active row 4
Conditional formatting with time 4
Highlight changes within cell 1

Back
Top