Grouping Tenure based on criteria

zinah

Board Regular
Joined
Nov 28, 2018
Messages
224
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 13, 2021
Messages
199
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Jun 12, 2014
Messages
58,139
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

zinah

Board Regular
Joined
Nov 28, 2018
Messages
224
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 28, 2018
Messages
224
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
58,139
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top