Determining If a Date is between Other Dates in excel

0
votes

I want to compare and identify if a date is between dates in a separate table. 

Start          End  Project Employee
1-Jan-13    31-Dec-13   10      2
1-Jan-14    31-Dec-14   10      2
1-Jan-15    31-Dec-15   12      2
1-Jan-16    31-Dec-16   12      2
1-Jan-17    31-Dec-17   14      2
1-Jan-16    31-Dec-16   10      5
1-Jan-17    31-Dec-17   20      5

I am looking for the project id that employee was comparing the date with the date period available in the second table for the same employee.

Employee    Date     projectid
2         1-Jul-16      ?
2         1-Dec-16      ?
2         1-May-17      ?
5         1-Jul-16      ?
5         1-Dec-16      ?

Please help. I tried LOOKUP formula but couldnt control the cell range dynamically. =LOOKUP(Date,start:end,project:project)

2
exceldateexcel-formulavlookup

2 Answers

0
votes

Since the projectid is numeric, you can use:

=AGGREGATE(14,6,(I2=Employee)*(J2>=Start)*(J2<=End)*Project,1)

where 

Employee    refers to:  =Sheet1!$D$2:$D$8
End         refers to:  =Sheet1!$B$2:$B$8
Project     refers to:  =Sheet1!$C$2:$C$8
Start       refers to:  =Sheet1!$A$2:$A$8

If the project was text, you would modify the formula to return the row number of the table, so as to look that up with the INDEX function.

eg: with your table column headers in row 1:

=INDEX(Project,AGGREGATE(14,6,(I2=Employee)*(J2>Start)*(J2<=End)*ROW(Project),1)-1)

If you have an older version of Excel, which does not have the AGGREGATE function, you can use:

=LOOKUP(1E+307,1/((I2=Employee)*(J2>=Start)*(J2<=End))*Project)

enter image description here

Oh, and if your table will be continually expanding, you could either set your various ranges to be larger than the largest expected range; or you could create a table and use Structured References.

0
votes

Here is another solution, a bit manual but still works.

If you use AND function to compare your cell between two different cell, you will get either TRUE or FALSE.

Considering your Start and End columns are B and C columns and A column is your number column (Titles are on 1st row):

ID  Start   End Project Employee
1   1-Jan-13    31-Dec-13   10  2
2   1-Jan-14    31-Dec-14   10  2
3   1-Jan-15    31-Dec-15   12  2
4   1-Jan-16    31-Dec-16   12  2
5   1-Jan-17    31-Dec-17   14  2
6   1-Jan-16    31-Dec-16   10  5
7   1-Jan-17    31-Dec-17   20  5

And the other table is on B C and D columns (Titles are on 11th row.)

If you copy this formula to somewhere you will get the project ID number or 0 (which is anyone)

=IF(AND(C12>$B$2,C12<$C$2),1,IF(AND(C12>$B$3,C12<$C$3),2,IF(AND(C12>$B$4,C12<$C$4),3,IF(AND(C12>$B$5,C12<$C$5),4,IF(AND(C12>$B$6,C12<$C$6),5,IF(AND(C12>$B$7,C12<$C$7),6,IF(AND(C12>$B$8,C12<$C$8),7,0)))))))

And just simply fill the cells to the down and the other ones will automatically pop-up.