alderman
New member
- Joined
- Jan 23, 2014
- Messages
- 19
- Reaction score
- 0
- Points
- 0
I have 2 worksheets I am working from.
The first contains a list of date ranges. See the table below for examples, the data could be a single date range (which would be easy in the end), or consecutive dates ranges in the example of identifier 222222, or non consecutive date ranges as in the example of identifier 333333.
The second worksheets contains a single date with corresponding identifiers
What I would like to do is check the date by the first worksheet and if it is within the date range specified I want to have another column(In Red) that will tell me if it is on the list or not.
This would be easy if there was only 1 date range; I could use VLOOKUP and an IF(AND()) equation to work it out
This would still be within my limits if all the dates were consecutive; that way I could do a pivotable and take the minimum of the start dates and max of the end dates to create one date range.
But with non-consecutive date ranges I am stumped at where to go to next.
HELP...
The first contains a list of date ranges. See the table below for examples, the data could be a single date range (which would be easy in the end), or consecutive dates ranges in the example of identifier 222222, or non consecutive date ranges as in the example of identifier 333333.
Identifier | Start Date | End Date |
111111 | 01-Jan-2014 | 17-Mar-2014 |
222222 | 12-Jan-2014 | 14-Feb-2014 |
222222 | 15-Feb-2014 | 01-Apr-2014 |
333333 | 16-Jan-2014 | 15-March-2014 |
333333 | 01-Apr-2014 | 13-May-2014 |
333333 | 27-May-2014 | 28-Jun-2014 |
The second worksheets contains a single date with corresponding identifiers
Identifier | Date | Include? |
111111 | 14-Feb-2014 | No |
111111 | 01-Apr-2014 | Yes |
222222 | 01-Apr-2014 | No |
222222 | 15-Jun-2014 | Yes |
333333 | 01-Apr-2014 | No |
333333 | 15-May-2014 | Yes |
What I would like to do is check the date by the first worksheet and if it is within the date range specified I want to have another column(In Red) that will tell me if it is on the list or not.
This would be easy if there was only 1 date range; I could use VLOOKUP and an IF(AND()) equation to work it out
This would still be within my limits if all the dates were consecutive; that way I could do a pivotable and take the minimum of the start dates and max of the end dates to create one date range.
But with non-consecutive date ranges I am stumped at where to go to next.
HELP...