Calculate Tenure Categories

[zinah]

Hi,

I need to calculate the tenure categories based on category table in my sample data below. Anyone could suggest a formula?

Tenure Category Calc.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Hire Date	Termination Date	Tenure Category									Tenure Category
2	7/18/2017	3/31/2022										< 2 Months
3	5/13/2019	3/25/2022										< 6 Months
4	2/1/2021	3/24/2022										< 1 Yr
5	7/1/2020	3/17/2022										1-2 Yrs
6	11/20/2018	3/11/2022										2-5 Yrs
7	7/13/2020	3/11/2022										5-10 Yrs
8	8/12/2020	3/11/2022										> 10 Yrs
9	8/17/2020	3/11/2022
10	6/7/2021	3/11/2022
11	5/1/2005	3/8/2022
12	3/4/2013	3/8/2022
13	5/12/2014	3/8/2022
14	6/12/2017	3/8/2022
15	3/1/2021	3/2/2022
16	11/20/2017	3/1/2022
17	9/7/2021	2/28/2022
18	5/8/2017	2/25/2022
19	5/20/2019	2/25/2022
20	2/25/2021	2/25/2022
21	1/16/2018	2/22/2022
22	3/1/2011	11/30/2021
23	7/18/2017	3/31/2022
24
Sheet1

Thank you!

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Hire Date	Termination Date	Tenure Category									Tenure Category
2	18/07/2017	31/03/2022	2-5 Yrs								0	< 2 Months
3	13/05/2019	25/03/2022	2-5 Yrs								2	< 6 Months
4	01/02/2021	24/03/2022	1-2 Yrs								6	< 1 Yr
5	01/07/2020	17/03/2022	1-2 Yrs								12	1-2 Yrs
6	20/11/2018	11/03/2022	2-5 Yrs								24	2-5 Yrs
7	13/07/2020	11/03/2022	1-2 Yrs								60	5-10 Yrs
8	12/08/2020	11/03/2022	1-2 Yrs								100000	> 10 Yrs
9	17/08/2020	11/03/2022	1-2 Yrs
10	07/06/2021	11/03/2022	< 1 Yr
11	01/05/2005	08/03/2022	5-10 Yrs
12	04/03/2013	08/03/2022	5-10 Yrs
13	12/05/2014	08/03/2022	5-10 Yrs
14	12/06/2017	08/03/2022	2-5 Yrs
15	01/03/2021	02/03/2022	1-2 Yrs
16	20/11/2017	01/03/2022	2-5 Yrs
17	07/09/2021	28/02/2022	< 6 Months
18	08/05/2017	25/02/2022	2-5 Yrs
19	20/05/2019	25/02/2022	2-5 Yrs
20	25/02/2021	25/02/2022	1-2 Yrs
21	16/01/2018	22/02/2022	2-5 Yrs
22	01/03/2011	30/11/2021	5-10 Yrs
23	18/07/2017	31/03/2022	2-5 Yrs
24
Master

Cell Formulas
Range	Formula
C2:C23	C2	=VLOOKUP(DATEDIF(A2,B2,"m"),$K$2:$L$8,2,1)

 

[zinah]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Hire Date	Termination Date	Tenure Category									Tenure Category
2	18/07/2017	31/03/2022	2-5 Yrs								0	< 2 Months
3	13/05/2019	25/03/2022	2-5 Yrs								2	< 6 Months
4	01/02/2021	24/03/2022	1-2 Yrs								6	< 1 Yr
5	01/07/2020	17/03/2022	1-2 Yrs								12	1-2 Yrs
6	20/11/2018	11/03/2022	2-5 Yrs								24	2-5 Yrs
7	13/07/2020	11/03/2022	1-2 Yrs								60	5-10 Yrs
8	12/08/2020	11/03/2022	1-2 Yrs								100000	> 10 Yrs
9	17/08/2020	11/03/2022	1-2 Yrs
10	07/06/2021	11/03/2022	< 1 Yr
11	01/05/2005	08/03/2022	5-10 Yrs
12	04/03/2013	08/03/2022	5-10 Yrs
13	12/05/2014	08/03/2022	5-10 Yrs
14	12/06/2017	08/03/2022	2-5 Yrs
15	01/03/2021	02/03/2022	1-2 Yrs
16	20/11/2017	01/03/2022	2-5 Yrs
17	07/09/2021	28/02/2022	< 6 Months
18	08/05/2017	25/02/2022	2-5 Yrs
19	20/05/2019	25/02/2022	2-5 Yrs
20	25/02/2021	25/02/2022	1-2 Yrs
21	16/01/2018	22/02/2022	2-5 Yrs
22	01/03/2011	30/11/2021	5-10 Yrs
23	18/07/2017	31/03/2022	2-5 Yrs
24
Master

Cell Formulas
Range	Formula
C2:C23	C2	=VLOOKUP(DATEDIF(A2,B2,"m"),$K$2:$L$8,2,1)

Thank you so much, that worked perfectly great!

 

[Fluff]

You're welcome & thanks for the feedback.