Tenure Grouping (choosing the right months range)

[zinah]

Hi,

I'm trying to group dates under tenure grouping and some group members here helped with suggesting the formula I'm using in my below example, but I noticed the tenure category grouping has some issues as it's not choosing the right group when the months of service is greater than 5 years. Any suggestion to fix this issue and get the right category from the table below?

Book2
	A	B	C	D	E	F	G	H	I	J
1	Date	Tenure Category	Problem							Tenure Category
2	1/30/1995	5-10 Yrs	this one should be more than 10 years not this category						0	< 2 Months
3	1/1/1998	5-10 Yrs							2	< 6 Months
4	1/11/1999	5-10 Yrs							6	< 1 Yr
5	1/25/1999	5-10 Yrs							12	1-2 Yrs
6	1/14/2002	5-10 Yrs							24	2-5 Yrs
7	1/7/2002	5-10 Yrs							60	5-10 Yrs
8	1/27/2003	5-10 Yrs							10000	> 10 Yrs
9	1/28/2003	5-10 Yrs
10	1/27/2004	5-10 Yrs
11	1/1/2004	5-10 Yrs
12	1/4/2005	5-10 Yrs
13	1/31/2005	5-10 Yrs
14	1/23/2005	5-10 Yrs
15	1/3/2005	5-10 Yrs
16	1/18/2005	5-10 Yrs
17	6/26/2005	5-10 Yrs
18	1/4/2005	5-10 Yrs
19	1/31/2005	5-10 Yrs
20	1/17/2005	5-10 Yrs
21	1/5/2005	5-10 Yrs
22	1/17/2006	5-10 Yrs
23	1/23/2006	5-10 Yrs
24	1/1/2006	5-10 Yrs
25	1/9/2006	5-10 Yrs
26	1/17/2006	5-10 Yrs
27	1/2/2006	5-10 Yrs
28	1/23/2006	5-10 Yrs
29	1/17/2007	5-10 Yrs
30	1/8/2007	5-10 Yrs
31	5/27/2007	5-10 Yrs
32	1/8/2007	5-10 Yrs
33	1/2/2007	5-10 Yrs
34	1/28/2008	5-10 Yrs
35	1/21/2008	5-10 Yrs
36	1/28/2008	5-10 Yrs
37	1/2/2008	5-10 Yrs
38	1/2/2008	5-10 Yrs
39	1/11/2010	5-10 Yrs
40	1/1/2010	5-10 Yrs
41	1/4/2010	5-10 Yrs
42	1/25/2010	5-10 Yrs
43	1/25/2010	5-10 Yrs
44	1/25/2010	5-10 Yrs
45	1/4/2010	5-10 Yrs
46	1/11/2010	5-10 Yrs
47	1/4/2010	5-10 Yrs
48	1/25/2010	5-10 Yrs
49	1/18/2010	5-10 Yrs
50	1/28/2010	5-10 Yrs
51	1/18/2010	5-10 Yrs
52	8/14/2017	2-5 Yrs
53	10/12/2017	2-5 Yrs
54	7/10/2017	2-5 Yrs
55	7/24/2017	2-5 Yrs
56	6/8/2017	2-5 Yrs
57	6/19/2017	2-5 Yrs
58	6/12/2017	2-5 Yrs
59	6/26/2017	2-5 Yrs
60	6/5/2017	2-5 Yrs
61	6/19/2017	2-5 Yrs
62	6/5/2017	2-5 Yrs
63	6/22/2017	2-5 Yrs
64	6/5/2017	2-5 Yrs
65	6/14/2017	2-5 Yrs
66	6/19/2017	2-5 Yrs
67	6/28/2017	2-5 Yrs
68	6/19/2017	2-5 Yrs
69	6/16/2017	2-5 Yrs
70	7/1/2017	2-5 Yrs
71	7/31/2017	2-5 Yrs
72	7/3/2017	2-5 Yrs
73	7/10/2017	2-5 Yrs
Sheet1

Cell Formulas
Range	Formula
B2:B73	B2	=VLOOKUP(DATEDIF(A2,TODAY(),"M"),$I$2:$J$8,2,1)

 

[etaf]

why 10000 for >10years
10years x 12 months = 120

change >10yrs to 120
NOT 10000

i had to change the dates as a few came over as text

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Tenure Category	Problem							Tenure Category
2	1/30/95	> 10 Yrs	this one should be more than 10 years not this category	328		120			0	< 2 Months
3	3/30/93	> 10 Yrs							2	< 6 Months
4	6/30/94	> 10 Yrs							6	< 1 Yr
5	9/30/95	> 10 Yrs							12	1-2 Yrs
6	12/30/96	> 10 Yrs							24	2-5 Yrs
7	3/30/98	> 10 Yrs							60	5-10 Yrs
8	6/30/99	> 10 Yrs							120	> 10 Yrs
9	9/30/00	> 10 Yrs
10	12/30/01	> 10 Yrs
11	3/30/03	> 10 Yrs
12	6/30/04	> 10 Yrs
13	9/30/05	> 10 Yrs
14	12/30/06	> 10 Yrs
15	3/30/08	> 10 Yrs
16	6/30/09	> 10 Yrs
17	9/30/10	> 10 Yrs
18	12/30/11	> 10 Yrs
19	3/30/13	5-10 Yrs
20	6/30/14	5-10 Yrs
21	9/30/15	5-10 Yrs
22	12/30/16	5-10 Yrs
23	3/30/18	2-5 Yrs
24	6/30/19	2-5 Yrs
25	9/30/20	1-2 Yrs
26	12/30/21	< 6 Months
27	1/2/22	< 6 Months
28	2/3/22	< 6 Months
29	3/7/22	< 6 Months
30	4/8/22	< 2 Months
31	5/10/22	< 2 Months
Sheet3

Cell Formulas
Range	Formula
D2	D2	=DATEDIF(A2,TODAY(),"M")
F2	F2	=12*10
B2:B31	B2	=VLOOKUP(DATEDIF(A2,TODAY(),"M"),$I$2:$J$8,2,1)

 

[zinah]

why 10000 for >10years
10years x 12 months = 120

change >10yrs to 120
NOT 10000

i had to change the dates as a few came over as text

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Tenure Category	Problem							Tenure Category
2	1/30/95	> 10 Yrs	this one should be more than 10 years not this category	328		120			0	< 2 Months
3	3/30/93	> 10 Yrs							2	< 6 Months
4	6/30/94	> 10 Yrs							6	< 1 Yr
5	9/30/95	> 10 Yrs							12	1-2 Yrs
6	12/30/96	> 10 Yrs							24	2-5 Yrs
7	3/30/98	> 10 Yrs							60	5-10 Yrs
8	6/30/99	> 10 Yrs							120	> 10 Yrs
9	9/30/00	> 10 Yrs
10	12/30/01	> 10 Yrs
11	3/30/03	> 10 Yrs
12	6/30/04	> 10 Yrs
13	9/30/05	> 10 Yrs
14	12/30/06	> 10 Yrs
15	3/30/08	> 10 Yrs
16	6/30/09	> 10 Yrs
17	9/30/10	> 10 Yrs
18	12/30/11	> 10 Yrs
19	3/30/13	5-10 Yrs
20	6/30/14	5-10 Yrs
21	9/30/15	5-10 Yrs
22	12/30/16	5-10 Yrs
23	3/30/18	2-5 Yrs
24	6/30/19	2-5 Yrs
25	9/30/20	1-2 Yrs
26	12/30/21	< 6 Months
27	1/2/22	< 6 Months
28	2/3/22	< 6 Months
29	3/7/22	< 6 Months
30	4/8/22	< 2 Months
31	5/10/22	< 2 Months
Sheet3

Cell Formulas
Range	Formula
D2	D2	=DATEDIF(A2,TODAY(),"M")
F2	F2	=12*10
B2:B31	B2	=VLOOKUP(DATEDIF(A2,TODAY(),"M"),$I$2:$J$8,2,1)

Thank you so much, that worked perfectly great!

 

[etaf]

you are welcome