Calculating Period by if funtcion

[gam2086]

I have a table below for period.

Sheet 1

[TABLE="width: 500"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Period[/TD]
[/TR]
[TR]
[TD]2/4/2018[/TD]
[TD]3/3/2018[/TD]
[TD]Period 1[/TD]
[/TR]
[TR]
[TD]3/4/2018[/TD]
[TD]3/31/2018[/TD]
[TD]Period 2[/TD]
[/TR]
</tbody>[/TABLE]

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.


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Periods start →​

[/td][td="bgcolor:#FFFF00"]

2/4/2018​

[/td][td]

Hire Date​

[/td][td]

Hire Periods​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td][/td][td][/td][td]

2/15/2018​

[/td][td]

1​

[/td][td]

In D2: =MATCH(C2,EDATE($B$1,INDEX(ROW($1:$100)-1,0)),1)​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td][/td][td]

3/15/2018​

[/td][td]

2​

[/td][td][/td][/tr]
[/table]

 

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