If a date is between 2 different Dates then Return Week Number

[SiuGuy007]

I am not 100% sure how to write the formula for this? On Sheet 1, I have various dates for new hired EE's. On Sheet 2, I have a list of "From Date" and "To Date", with the corresponding Week number.

If the New Hired EE date falls between the From and To Dates, I need to have the result return the week number.

[TABLE="width: 460"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]From Date[/TD]
[TD]To Date[/TD]
[TD]Week number[/TD]
[TD][/TD]
[TD]New Hire EE[/TD]
[/TR]
[TR]
[TD]January 1, 2018[/TD]
[TD]January 7, 2018[/TD]
[TD]Week 01[/TD]
[TD][/TD]
[TD]1/4/2018[/TD]
[/TR]
[TR]
[TD]January 8, 2018[/TD]
[TD]January 14, 2018[/TD]
[TD]Week 02[/TD]
[TD][/TD]
[TD]1/5/2018[/TD]
[/TR]
[TR]
[TD]January 15, 2018[/TD]
[TD]January 21, 2018[/TD]
[TD]Week 03[/TD]
[TD][/TD]
[TD]1/6/2018[/TD]
[/TR]
[TR]
[TD]January 22, 2018[/TD]
[TD]January 28, 2018[/TD]
[TD]Week 04[/TD]
[TD][/TD]
[TD]1/7/2018[/TD]
[/TR]
[TR]
[TD]January 29, 2018[/TD]
[TD]February 4, 2018[/TD]
[TD]Week 05[/TD]
[TD][/TD]
[TD]1/8/2018[/TD]
[/TR]
</tbody>[/TABLE]

Any assistance is greatly appreciated.

SIUGuy007

 

[jtakw]

Hi,

You can use LOOKUP or INDEX/MATCH:

Book1
	A	B	C	D	E	F	G
1	From Date	To Date	Week number		New Hire EE	LOOKUP	INDEX/MATCH
2	1-Jan-18	7-Jan-18	Week 01		1/4/2018	Week 01	Week 01
3	8-Jan-18	14-Jan-18	Week 02		1/5/2018	Week 01	Week 01
4	15-Jan-18	21-Jan-18	Week 03		1/6/2018	Week 01	Week 01
5	22-Jan-18	28-Jan-18	Week 04		1/7/2018	Week 01	Week 01
6	29-Jan-18	4-Feb-18	Week 05		1/8/2018	Week 02	Week 02
Sheet44

Cell Formulas
Range	Formula
F2	=LOOKUP(E2,A$2:A$6,C$2:C$6)
G2	=INDEX(C$2:C$6,MATCH(E2,A$2:A$6,1))

Either way, formula copied down.

 

[SiuGuy007]

My apologies for the delay in acknowledging your prompt reply and answer to my question. Very much appreciated. The Lookup formula did not work, however, the Match formula did.

Thanks again,

SIUGuy

 

[SiuGuy007]

One other quick question. Instead of returning the week number (E2), I would like to count the total number of dates between the from and to periods.

 

[jtakw]

I'm not sure I understanding your new question.

Are you saying the number of days FROM and TO columns A & B, each line separately, or Total (A2 to B6)?
Or are you saying the number of days within FROM and TO COLUMNS A & B, Starting with Hire date (column E), each line separately?

Please give sample and desired results.

 

[SiuGuy007]

Thank you for your reply. I guess I should say it this way. If an EE Hire date is between 2 different Dates (From and To Date) then Return the count. Example below.

[TABLE="width: 443"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]EE Hire Date[/TD]
[TD]From Date[/TD]
[TD]To Date[/TD]
[TD] Week #[/TD]
[TD]Results[/TD]
[/TR]
[TR]
[TD]4/6/2018[/TD]
[TD]4/2/2018[/TD]
[TD="align: right"]4/8/2018[/TD]
[TD] Week 14[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4/14/2018[/TD]
[TD]4/9/2018[/TD]
[TD="align: right"]4/15/2018[/TD]
[TD] Week 15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4/25/2018[/TD]
[TD]4/16/2018[/TD]
[TD="align: right"]4/22/2018[/TD]
[TD] Week 16[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4/25/2018[/TD]
[TD]4/23/2018[/TD]
[TD="align: right"]4/29/2018[/TD]
[TD] Week 17[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4/25/2018[/TD]
[TD]4/30/2018[/TD]
[TD="align: right"]5/6/2018[/TD]
[TD] Week 18[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

I'm not following the logic/math in your sample results above, please explain How you got 1, 1, 0, 3, 1?

 

[SiuGuy007]

Under EE Hire date, there is 1 new hire on 4/6/18. This is the only new hire date that fell between 4/2/18 to 4/8/18. 4/6/18 equals 1.
Under EE Hire date, there 3 new hires on 4/25/18. These 3 new hire dates fell between 4/23/18 to 4/29/18. Those 3 dates equal 3.
And so on!

 

[jtakw]

Thank you for your reply. I guess I should say it this way. If an EE Hire date is between 2 different Dates (From and To Date) then Return the count. Example below.

[TABLE="width: 443"]
<tbody>[TR]
[TD]EE Hire Date
[/TD]
[TD]From Date
[/TD]
[TD]To Date
[/TD]
[TD] Week #
[/TD]
[TD]Results
[/TD]
[/TR]
[TR]
[TD]4/6/2018
[/TD]
[TD]4/2/2018
[/TD]
[TD="align: right"]4/8/2018
[/TD]
[TD] Week 14
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]4/14/2018
[/TD]
[TD]4/9/2018
[/TD]
[TD="align: right"]4/15/2018
[/TD]
[TD] Week 15
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]4/25/2018
[/TD]
[TD]4/16/2018
[/TD]
[TD="align: right"]4/22/2018
[/TD]
[TD] Week 16
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/25/2018
[/TD]
[TD]4/23/2018
[/TD]
[TD="align: right"]4/29/2018
[/TD]
[TD] Week 17
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]4/25/2018
[/TD]
[TD]4/30/2018
[/TD]
[TD="align: right"]5/6/2018
[/TD]
[TD] Week 18
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

But you're showing 1 hire in red above, that's what threw me off.

 

[SiuGuy007]

My bad!