# Grouping Tenure based on criteria

#### zinah

##### Board Regular
Hello!

I have below sample table, and what I need is to group service date based on criteria tenure, is there any suggestion for a good formula?

Book16
ABCD
1Continuous Service DateTenure GroupTenure Groups Criteria
211/7/2016<2 years
31/30/20172-6
410/15/20166-10
510/24/201610-15
612/1/201615+
711/3/2016
810/3/2016
911/3/2016
105/1/2019
1112/1/2016
1210/15/2016
1311/14/2016
1411/7/2016
1511/14/2016
1611/7/2016
1712/5/2016
181/1/2017
193/1/2017
203/6/2017
213/6/2017
223/21/2017
233/6/2017
243/20/2017
253/15/2017
263/15/2017
271/2/2017
2811/7/2016
294/6/2015
301/21/1995
3110/29/1994
3211/22/1993
336/13/1994
347/25/2012
359/5/1995
3611/13/2006
375/8/1996
386/9/1997
391/24/1998
401/30/2017
411/11/1999
4210/19/2020
437/20/2010
447/6/2009
457/15/2001
4610/29/2013
479/25/2000
4812/11/2002
495/3/2010
506/17/2002
516/16/2003
Sheet1

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### JamesCanale

##### Board Regular
MrExcelPlayground.xlsm
ABCD
1Continuous Service DateTenure GroupTenure Groups Criteria
211/7/20162-60<2 years
31/30/20172-622-6
410/15/20162-666-10
510/24/20162-61010-15
612/1/20162-61515+
711/3/20162-6
810/3/20162-6
911/3/20162-6
105/1/2019<2 years
1112/1/20162-6
1210/15/20162-6
1311/14/20162-6
1411/7/20162-6
1511/14/20162-6
1611/7/20162-6
1712/5/20162-6
181/1/20172-6
193/1/20172-6
203/6/20172-6
213/6/20172-6
223/21/20172-6
233/6/20172-6
243/20/20172-6
253/15/20172-6
263/15/20172-6
271/2/20172-6
2811/7/20162-6
294/6/20152-6
301/21/199515+
3110/29/199415+
3211/22/199315+
336/13/199415+
347/25/20126-10
359/5/199515+
3611/13/200610-15
375/8/199615+
386/9/199715+
391/24/199815+
401/30/20172-6
411/11/199915+
4210/19/2020<2 years
437/20/201010-15
447/6/200910-15
457/15/200115+
4610/29/20136-10
479/25/200015+
4812/11/200215+
495/3/201010-15
506/17/200215+
516/16/200315+
Sheet24
Cell Formulas
RangeFormula
B2:B51B2=VLOOKUP(YEARFRAC(A2,TODAY()),\$C\$2:\$D\$6,2,TRUE)

#### Fluff

##### MrExcel MVP, Moderator
You have overlapping sections, so I have changed the ranges slightly to remove them
+Fluff 1.xlsm
ABCDE
1Continuous Service DateTenure GroupTenure Groups Criteria
207/11/20162-6 years0<2 years
330/01/20172-6 years22-6 years
415/10/20162-6 years77-10 years
524/10/20162-6 years1111-15 years
601/12/20162-6 years16>15 years
703/11/20162-6 years
803/10/20162-6 years
903/11/20162-6 years
1001/05/2019<2 years
1101/12/20162-6 years
1215/10/20162-6 years
1314/11/20162-6 years
1407/11/20162-6 years
1514/11/20162-6 years
1607/11/20162-6 years
1705/12/20162-6 years
1801/01/20172-6 years
1901/03/20172-6 years
2006/03/20172-6 years
2106/03/20172-6 years
2221/03/20172-6 years
2306/03/20172-6 years
2420/03/20172-6 years
2515/03/20172-6 years
2615/03/20172-6 years
2702/01/20172-6 years
2807/11/20162-6 years
2906/04/20152-6 years
3021/01/1995>15 years
3129/10/1994>15 years
3222/11/1993>15 years
3313/06/1994>15 years
3425/07/20127-10 years
3505/09/1995>15 years
3613/11/200611-15 years
3708/05/1996>15 years
3809/06/1997>15 years
3924/01/1998>15 years
4030/01/20172-6 years
4111/01/1999>15 years
4219/10/2020<2 years
4320/07/20107-10 years
4406/07/200911-15 years
4515/07/2001>15 years
4629/10/20137-10 years
4725/09/2000>15 years
4811/12/2002>15 years
4903/05/20107-10 years
5017/06/2002>15 years
5116/06/2003>15 years
Test
Cell Formulas
RangeFormula
B2:B51B2=VLOOKUP(DATEDIF(A2,TODAY(),"y"),\$D\$2:\$E\$6,2,1)

#### zinah

##### Board Regular
MrExcelPlayground.xlsm
ABCD
1Continuous Service DateTenure GroupTenure Groups Criteria
211/7/20162-60<2 years
31/30/20172-622-6
410/15/20162-666-10
510/24/20162-61010-15
612/1/20162-61515+
711/3/20162-6
810/3/20162-6
911/3/20162-6
105/1/2019<2 years
1112/1/20162-6
1210/15/20162-6
1311/14/20162-6
1411/7/20162-6
1511/14/20162-6
1611/7/20162-6
1712/5/20162-6
181/1/20172-6
193/1/20172-6
203/6/20172-6
213/6/20172-6
223/21/20172-6
233/6/20172-6
243/20/20172-6
253/15/20172-6
263/15/20172-6
271/2/20172-6
2811/7/20162-6
294/6/20152-6
301/21/199515+
3110/29/199415+
3211/22/199315+
336/13/199415+
347/25/20126-10
359/5/199515+
3611/13/200610-15
375/8/199615+
386/9/199715+
391/24/199815+
401/30/20172-6
411/11/199915+
4210/19/2020<2 years
437/20/201010-15
447/6/200910-15
457/15/200115+
4610/29/20136-10
479/25/200015+
4812/11/200215+
495/3/201010-15
506/17/200215+
516/16/200315+
Sheet24
Cell Formulas
RangeFormula
B2:B51B2=VLOOKUP(YEARFRAC(A2,TODAY()),\$C\$2:\$D\$6,2,TRUE)
Thank you so much, your formula worked perfectly great!

#### zinah

##### Board Regular
You have overlapping sections, so I have changed the ranges slightly to remove them
+Fluff 1.xlsm
ABCDE
1Continuous Service DateTenure GroupTenure Groups Criteria
207/11/20162-6 years0<2 years
330/01/20172-6 years22-6 years
415/10/20162-6 years77-10 years
524/10/20162-6 years1111-15 years
601/12/20162-6 years16>15 years
703/11/20162-6 years
803/10/20162-6 years
903/11/20162-6 years
1001/05/2019<2 years
1101/12/20162-6 years
1215/10/20162-6 years
1314/11/20162-6 years
1407/11/20162-6 years
1514/11/20162-6 years
1607/11/20162-6 years
1705/12/20162-6 years
1801/01/20172-6 years
1901/03/20172-6 years
2006/03/20172-6 years
2106/03/20172-6 years
2221/03/20172-6 years
2306/03/20172-6 years
2420/03/20172-6 years
2515/03/20172-6 years
2615/03/20172-6 years
2702/01/20172-6 years
2807/11/20162-6 years
2906/04/20152-6 years
3021/01/1995>15 years
3129/10/1994>15 years
3222/11/1993>15 years
3313/06/1994>15 years
3425/07/20127-10 years
3505/09/1995>15 years
3613/11/200611-15 years
3708/05/1996>15 years
3809/06/1997>15 years
3924/01/1998>15 years
4030/01/20172-6 years
4111/01/1999>15 years
4219/10/2020<2 years
4320/07/20107-10 years
4406/07/200911-15 years
4515/07/2001>15 years
4629/10/20137-10 years
4725/09/2000>15 years
4811/12/2002>15 years
4903/05/20107-10 years
5017/06/2002>15 years
5116/06/2003>15 years
Test
Cell Formulas
RangeFormula
B2:B51B2=VLOOKUP(DATEDIF(A2,TODAY(),"y"),\$D\$2:\$E\$6,2,1)
As usual thank you so much for stepping in and suggest a great formula, your formula worked and thank you for adjusting the ranges

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
33
Views
598
Replies
0
Views
28
Replies
8
Views
215
Replies
1
Views
94
Replies
2
Views
299

1,132,646
Messages
5,654,567
Members
418,142
Latest member
peterappiahkubi

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back