List Matching with Dates: Using Dates as filters

Is there a better forum to ask this question? Is this question more appropriate for the programming forum?

Let me try asking a different question which may helpful for me. I have attached another file. This time I would like to know if and how I can take advantage of the advanced filters feature to ensure that only date ranges including 7/16/1999 are shown? What do and, or, if, ><= type of logic do I put under the "start" and "end" date at the top to filter the date ranges? I would think some sort of > < = type logic would make this work. Then again my excel skills are beginner's level.

I think if I can filter the date ranges then I can just do regular old matching to see if the other criteria are met.

Originally Posted by Thawk

A restaurant has two lists of people it has employed (see attached). I want to see if the people on list one are on list two. I need to come up with a formula that gives TRUE for each person on list one who is on list two and false for each person on list one who is not on list two. Several caveats: 1) there are people on list one who aren't on list two and vice versa, 2) the people have several defining characteristics which need to be met, and 3) dates are involved.

With respect to point three, TRUE should only appear if the people on list one are on list two and if they were employed by the organization on a specific date (ex: 3/5/1996); in order for the TRUE statement to appear in the G column, the person should not only be on list two but also be employed on a specific date. The specific date should fall within the start and end employment contract.

Let me know if this is confusing description and more clarity is required.

Thanks in advance.

See attached file.

The spreadsheet has several people with the same name and some even with the same name and job. Maybe it would be better for you to use the formula in steve's spreadsheet but to add in the month of birth...

This would help to stop any confusion if people have simlar contract dates.

Let me think about this and get back to you.

My real issue is that I have two lists which I need to match. If I can filter the dates in one of the lists, I can ensure that each person will appear once on each list. So I feel like once I can filter for dates then I will be able to do regular old matching with multiple criteria, which another forum member kindly showed me how to do. Unfortunately I am unable to post the actual spreadsheet so have to rely on developing examples which will parallel what I will do.

I have attached a spreadsheet which uses the filter as a test of whether the date is included in the range. I have included all the possible cases I can think of in terms of testing the robustness of the filter.

Will this work? Are there any problems with using this technique as a way to filter out all the date ranges which do not include the date I want them to include?

Are there any cases I have not included?