Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing times in two tables
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. Id only need to return one comparison value to indicate the corresponding time frames in the two logs, but still Im really stumped. Any help would be much appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing times in two tables
=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: 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. Id only need to return one comparison value to indicate the corresponding time frames in the two logs, but still Im really stumped. Any help would be much appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing times in two tables
Suppose you have the times stored in Col A and Col B
=IF(B:B-A:AMINUTE(10),"close",IF(B:B-A:A<MINUTE(10),"close","")) -- If this post helps click Yes --------------- Jacob Skaria "Hugh" wrote: 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. Id only need to return one comparison value to indicate the corresponding time frames in the two logs, but still Im really stumped. Any help would be much appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing times in two tables
Hi Stefi,
Thanks for your help but thats not quite it. Ive tried your suggestion in combination with my previous attempts but without success. Ill 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. Ive 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 cant factor in the time variance between the tow lists. Can you help? Thanks, Hugh "Stefi" wrote: =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: 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. Id only need to return one comparison value to indicate the corresponding time frames in the two logs, but still Im really stumped. Any help would be much appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing times in two tables
Hi Jacob,
Thanks for your efforts but thats not quite what I want. Please see my response to Stefi, above. I hope you can help. Regards, Hugh "Jacob Skaria" wrote: Suppose you have the times stored in Col A and Col B =IF(B:B-A:AMINUTE(10),"close",IF(B:B-A:A<MINUTE(10),"close","")) -- If this post helps click Yes --------------- Jacob Skaria "Hugh" wrote: 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. Id only need to return one comparison value to indicate the corresponding time frames in the two logs, but still Im really stumped. Any help would be much appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing times in two tables
Perhaps you can explain us what you are trying to do there, Jacob, as
MINUTE(10) will return zero. Perhaps you need to look at Excel help for the MINUTE function to remind yourself what it does. -- David Biddulph "Jacob Skaria" wrote in message ... Suppose you have the times stored in Col A and Col B =IF(B:B-A:AMINUTE(10),"close",IF(B:B-A:A<MINUTE(10),"close","")) -- If this post helps click Yes --------------- Jacob Skaria "Hugh" wrote: 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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing times in two tables
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: Hi Stefi, Thanks for your help but thats not quite it. Ive tried your suggestion in combination with my previous attempts but without success. Ill 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. Ive 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 cant factor in the time variance between the tow lists. Can you help? Thanks, Hugh "Stefi" wrote: =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: 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. Id only need to return one comparison value to indicate the corresponding time frames in the two logs, but still Im really stumped. Any help would be much appreciated. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing times in two tables
Sorry.. That was my mistake....
-- If this post helps click Yes --------------- Jacob Skaria "David Biddulph" wrote: Perhaps you can explain us what you are trying to do there, Jacob, as MINUTE(10) will return zero. Perhaps you need to look at Excel help for the MINUTE function to remind yourself what it does. -- David Biddulph "Jacob Skaria" wrote in message ... Suppose you have the times stored in Col A and Col B =IF(B:B-A:AMINUTE(10),"close",IF(B:B-A:A<MINUTE(10),"close","")) -- If this post helps click Yes --------------- Jacob Skaria "Hugh" wrote: 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing times in two tables
Thanks Stefi. With a little manipulation, I got there.
You've saved me a whole heap of time. "Stefi" wrote: 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: Hi Stefi, Thanks for your help but thats not quite it. Ive tried your suggestion in combination with my previous attempts but without success. Ill 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. Ive 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 cant factor in the time variance between the tow lists. Can you help? Thanks, Hugh "Stefi" wrote: =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: 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. Id only need to return one comparison value to indicate the corresponding time frames in the two logs, but still Im really stumped. Any help would be much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing times.. | Excel Worksheet Functions | |||
Comparing two times | Excel Discussion (Misc queries) | |||
Comparing Calculated Times | Excel Worksheet Functions | |||
Comparing Times | Excel Discussion (Misc queries) | |||
Comparing Times | New Users to Excel |