Calculating Period by if funtcion

[gam2086]

I have a table below for period.

Sheet 1

Start Date	End Date	Period
2/4/2018	3/3/2018	Period 1
3/4/2018	3/31/2018	Period 2

<tbody>
</tbody>

Sheet 2

Has Hire date column C2 (2/15/18).

I am looking for help to whether I can use if formula to say 2/15/18 fall in Period 1, whereas if is 3/15/18 fall in period 2 and so on.

Please help. Looking for a solution as soon as possible.

thank you in advance

 

[Dave Patton]

Excel 2010
	A	B	C
1	Start	Emd	Period
2	4-Feb-18	3-Mar-18	Period 1
3	4-Mar-18	31-Mar-18	Period 2
4
5
6
7	Hire Date
8	15-Feb-18	Period 1
9
Sheet 1

Cell Formulas
Range	Formula
B8	=VLOOKUP(A8,'Sheet 1'!A2:C3,3.1)

=IF(AND(A8>='Sheet 1'!A2,A8<='Sheet 1'!B2),"Period 1",IF(AND(A8>='Sheet 1'!A3,A8<='Sheet 1'!B3),"Period 2",""))

 

[gam2086]

Hello Dave,

It is not working if =VLOOKUP(A8,'Sheet 1'!A2: C3,3.1).
Is this 3.1 or it column index 3 and True.
I thought lookup will look up for certain value only. It will not look up between those dates.
Not sure. Please let me know

Thank you for reply

 

[FlameRetired]

Does this do what you want?

The ROW($1:$100) is arbitrary. It makes room for a future range of 100 periods. If fewer are needed change the $100.

	A​	B​	C​	D​	E​
1​	Periods start →​	2/4/2018​	Hire Date​	Hire Periods​
2​			2/15/2018​	1​	In D2: =MATCH(C2,EDATE($B$1,INDEX(ROW($1:$100)-1,0)),1)​
3​			3/15/2018​	2​

 

[Dave Patton]

Excel 2010
	A	B	C
1	Start	End	Period
2	4-Feb-18	3-Mar-18	Period 1
3	4-Mar-18	31-Mar-18	Period 2
4
5
6
7	Hire Date
8	15-Feb-18	Period 1	Period 1
9
Sheet 1

Cell Formulas
Range	Formula
B8	=VLOOKUP(A8,'Sheet 1'!A2:C3,3,1)
C8	=IF(AND(A8>='Sheet 1'!A2,A8<='Sheet 1'!B2),"Period 1",IF(AND(A8>='Sheet 1'!A3,A8<='Sheet 1'!B3),"Period 2",""))

The formula had a "." instead of a ",".

I also included a space in the Sheet name per your example.

 

[gam2086]

Thank you Dave it worked

 

[Dave Patton]

Thanks for the feedback.

I had cataract surgery and correction; hope some day to be able to see better.