[Solved] Conditional Date Search

[BrianDP1977]

Hey all, here's my problem. I have a database with four columns (A thru D). Column A (A2:A10) consists of names, column B (B2:B10) consists of positions using text (i.e. Pilot1, Pilot2, MED), column C (C2:C10) consists of start dates, and column D (D2:D10) consists of end dates. Lastly, a specific date is entered into lets say cell F2. I need a formula that will search the defined database of names, positions, and dates and return the name of the person if he holds a specified position (let’s say Pilot1) and the entered date is between his/her start and end dates in the database. Right now I have the following formula:

{=INDEX(A2:A10,MATCH(1,(B2:B10="Pilot1")*(AND((F2>=MIN(C2:C10,D2:D10)),F2<=MAX(C2:C10,D2:D10))),0))}

However, this formula doesn’t seem to take the date condition into account (or the date condition is always returning true). It simply returns the name of the first person with a Pilot1 position designated that it encounters. Any help with this would be very appreciated.

 

[barry houdini]

Try this

=INDEX(A2:A10,MATCH(1,(B2:B10="Pilot1")*(F2>=C2:C10)*(F2<=D2:D10),0))

still an array formula to be entered with CTRL+SHIFT+ENTER

 

[BrianDP1977]

That did it. I knew it was a simple answer. Thanks so much for the help.

 

[BrianDP1977]

Okay, I need to add one more condition. Using the reference date (F2), I need to check to see if the start and end range of dates contains at least 15 days of the specified reference month.

Example: if the date (F2) is 11 SEP 05, I want to check the range of dates to see that there are at least 15 days in September covered (i.e. 5 SEP 05 – 1 DEC 05 would return TRUE, 31 JUN 05 – 11 SEP 05 would return FALSE but 31 JUN 05 – 15 SEP would return TRUE).

 

[BrianDP1977]

Got it working. Thanks.