Vlookup and comparing two date ranges

[Drewster1]

Hi guys,

I often search this forums for advice for issues, you guys have been great. Today I come to you with my own problem. \

My goal is to fill in 'Number of days' using a Vlookup function with the number of days from Date sheet 2. Still not sure what the best way to do it is, either specifying the number of days or sum product total days if the dates in Data Sheet 1 correspond with dates in the Data Sheet 2.

Thanks for the help Drew

Data sheet 1

	A	B	C	D	E
1	Employee ID	Leave start date	Leave end date	Overlapping dates (Y/N)	Number of days
2	200123	27/04/2011	6/01/2012
3	200123	16/01/2012	26/02/2012
4	200123	22/04/2014	27/05/2014
5	200456	01/03/2010	01/09/2010

<tbody>
</tbody>

Data sheet 2

	A	B	C	D
1	Employee ID	Start Date	End Date	Days taken
2	200123	4/05/2011	31/05/2011	26
3	200123	22/01/2012	29/01/2012	7
4	200123	11/05/2014	13/05/2014	2
5	200456	9/06/2010	11/06/2010	3

<tbody>
</tbody>

 

[venkat1926]

not clear what you want? my regional configuration compels me to enter date as m/d/yy
I have only formatted as x/m/yy. but actual entry you can see in formula bar.
sheet is like this

Sheet1

*	A	B	C	D	E
1	Employee ID	Leave start date	Leave end date	Overlapping dates (Y/N)	Number of days
2	200123	27-Apr-11	6-Jan-12	yes	254
3	200123	16-Jan-12	26-Feb-12	yes	41
4	200123	22-Apr-14	27-May-14	yes	35
5	200456	1-Mar-10	1-Sep-10	yes	184

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:85px;"><col style="width:106px;"><col style="width:102px;"><col style="width:159px;"><col style="width:106px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D2 =IF(AND(B2<=Sheet2!B2,Sheet1!C2>=Sheet2!C2),"yes","no") E2 =C2-B2 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


sheet 2 is

Sheet2

*	A	B	C	D
1	Employee ID	Start Date	End Date	Days taken
2	200123	4-May-11	31-May-11	26
3	200123	22-Jan-12	29-Jan-12	7
4	200123	11-May-14	13-May-14	2
5	200456	9-Jun-10	11-Jun-10	3

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:85px;"><col style="width:75px;"><col style="width:75px;"><col style="width:74px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


in sheet 1 see formulas in C2 ad D2 wich is copied down.
is this what you want?