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.

From Date	To Date	Week number		New Hire EE
January 1, 2018	January 7, 2018	Week 01		1/4/2018
January 8, 2018	January 14, 2018	Week 02		1/5/2018
January 15, 2018	January 21, 2018	Week 03		1/6/2018
January 22, 2018	January 28, 2018	Week 04		1/7/2018
January 29, 2018	February 4, 2018	Week 05		1/8/2018

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

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.

EE Hire Date	From Date	To Date	Week #	Results
4/6/2018	4/2/2018	4/8/2018	Week 14	1
4/14/2018	4/9/2018	4/15/2018	Week 15	1
4/25/2018	4/16/2018	4/22/2018	Week 16	0
4/25/2018	4/23/2018	4/29/2018	Week 17	3
4/25/2018	4/30/2018	5/6/2018	Week 18	1

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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.

EE Hire Date	From Date	To Date	Week #	Results
4/6/2018	4/2/2018	4/8/2018	Week 14	1
4/14/2018	4/9/2018	4/15/2018	Week 15	1
4/25/2018	4/16/2018	4/22/2018	Week 16
4/25/2018	4/23/2018	4/29/2018	Week 17	3
4/25/2018	4/30/2018	5/6/2018	Week 18	1

<tbody>
</tbody>

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

 

[SiuGuy007]

My bad!