IF statement that returns a category name based on start date & end date

[maxim642]

I'd like to be able to group employees that have left the company into "tenure groups" based on the length of time they were with the company.

The below table is an example of my dataset. The "tenure groups" are "0-6 months", "6-12 months", "1-5 years", and "5 years+". If an employee's end date is blank, that means they are still with the company (so perhaps a TODAY function would be inserted to calculate length of service). Thank you.

Employee ID	Start Date	End Date	Tenure Group
Emp2	01/12/2019	31/12/2021
Emp3	24/07/2016	01/11/2016
Emp4	11/04/2010	01/12/2019
Emp5	30/09/2021
Emp6	17/01/2022

 

[BSALV]

josros (1).xlsm
	A	B	C	D	E	F	G
1
2	Employee ID	Start Date	End Date	Tenure Group		months	description
3	Emp2	1/12/2019	31/12/2021	1-5 years		0	0-6 months
4	Emp3	24/07/2016	1/11/2016	0-6 months		6	6-12 months
5	Emp4	11/04/2010	1/12/2019	5 years+		12	1-5 years
6	Emp5	30/09/2021		0-6 months		60	5 years+
7	Emp6	17/01/2022		0-6 months
8
Blad1

Cell Formulas
Range	Formula
D3:D7	D3	=VLOOKUP(DATEDIF(B3,IF(C3="",TODAY(),C3),"m"),$F$3:$G$6,2,1)

 

[maxim642]

josros (1).xlsm
	A	B	C	D	E	F	G
1
2	Employee ID	Start Date	End Date	Tenure Group		months	description
3	Emp2	1/12/2019	31/12/2021	1-5 years		0	0-6 months
4	Emp3	24/07/2016	1/11/2016	0-6 months		6	6-12 months
5	Emp4	11/04/2010	1/12/2019	5 years+		12	1-5 years
6	Emp5	30/09/2021		0-6 months		60	5 years+
7	Emp6	17/01/2022		0-6 months
8
Blad1

Cell Formulas
Range	Formula
D3:D7	D3	=VLOOKUP(DATEDIF(B3,IF(C3="",TODAY(),C3),"m"),$F$3:$G$6,2,1)

Thank you very much, this is perfect.