# [Solved] Conditional Date Search

#### BrianDP1977

##### Board Regular
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.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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

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

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

Got it working. Thanks.

Replies
7
Views
158
Replies
10
Views
339
Replies
7
Views
202
Replies
5
Views
155
Replies
3
Views
1K

1,196,237
Messages
6,014,163
Members
441,807
Latest member
sjkenjalo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back