Match time from one column to another

[deante]

Hello All,

I know one of you all can help me with this problem. I have a worksheet with six columns. Tool, Start, End, Tool, Auto Start, Auto End. The sheet was made from two other worksheets. I want excel to search the worksheet for matches where the auto start and auto end time are within the Start and End times for the same Tool.

For example:


Tool Start End Tool Auto Start Auto End
1 0:00 1:00 2 2:15 2:45
1 1:30 2:15 2 3:05 3:52
2 2:00 3:00 3 5:55 6:05


The only return value should be the cell D2, or some other way of filtering the data, beacuse cells E2 and F2 fall between values in column B and C of the same row and have a matching Tool number.


If you have any questions or comments please do not hesitate to ask.

Thanks in Advance

D

 

[SkipVought]

Hi,

I put the two tables on separate sheets. They are not the same.

I named the ranges on the FIRST table: Tool, Start, End

The SECOND table I copies to A1 on another sheet. In column D & E ROW 2...
[tt]
D2: =SUMPRODUCT((Tool=$A2)*(Start<=$B2)*(End>=$B2))
E2: =SUMPRODUCT((Tool=$A2)*(Start<=$C2)*(End>=$C2))
[/tt]
and copied down thru the rows of data.

If you OR these 2 results it will show if ONE or the other is within the Start/End range; AND will show if BOTH are between the Start/End range.

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]

 

[deante]

I've tried what you suggested an keep receiving a #NUM! error message? Any other ways?

 

[SkipVought]

Are all your times REAL TIME values?

If you select the columns containing TIME and select the Format>Cells - Number Tab GENERAL format, then ALL your time formats should change to NUMERIC VALUES.

If not, you do not have REAL TIME values and the formula will burp.

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,
[sub]
[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion! [/sub]