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
[TABLE="width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee ID[/TD]
[TD]Leave start date[/TD]
[TD]Leave end date[/TD]
[TD]Overlapping dates (Y/N)[/TD]
[TD]Number of days[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]200123[/TD]
[TD]27/04/2011[/TD]
[TD]6/01/2012[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]200123[/TD]
[TD]16/01/2012[/TD]
[TD]26/02/2012[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]200123[/TD]
[TD]22/04/2014[/TD]
[TD]27/05/2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]200456[/TD]
[TD]01/03/2010[/TD]
[TD]01/09/2010[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Data sheet 2
[TABLE="width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee ID[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Days taken[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]200123[/TD]
[TD]4/05/2011[/TD]
[TD]31/05/2011[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]200123[/TD]
[TD]22/01/2012[/TD]
[TD]29/01/2012[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]200123[/TD]
[TD]11/05/2014[/TD]
[TD]13/05/2014[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]200456[/TD]
[TD]9/06/2010[/TD]
[TD]11/06/2010[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Employee ID	Leave start date	Leave end date	Overlapping dates (Y/N)	Number of days
yes
yes
yes
yes

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]200123[/TD]
[TD="align: right"]27-Apr-11[/TD]
[TD="align: right"]6-Jan-12[/TD]

[TD="align: right"]254[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]200123[/TD]
[TD="align: right"]16-Jan-12[/TD]
[TD="align: right"]26-Feb-12[/TD]

[TD="align: right"]41[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]200123[/TD]
[TD="align: right"]22-Apr-14[/TD]
[TD="align: right"]27-May-14[/TD]

[TD="align: right"]35[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]200456[/TD]
[TD="align: right"]1-Mar-10[/TD]
[TD="align: right"]1-Sep-10[/TD]

[TD="align: right"]184[/TD]

</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
Employee ID	Start Date	End Date	Days taken

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]200123[/TD]
[TD="align: right"]4-May-11[/TD]
[TD="align: right"]31-May-11[/TD]
[TD="align: right"]26[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]200123[/TD]
[TD="align: right"]22-Jan-12[/TD]
[TD="align: right"]29-Jan-12[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]200123[/TD]
[TD="align: right"]11-May-14[/TD]
[TD="align: right"]13-May-14[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]200456[/TD]
[TD="align: right"]9-Jun-10[/TD]
[TD="align: right"]11-Jun-10[/TD]
[TD="align: right"]3[/TD]

</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?