Years into groups

[zinah]

Hi!

I have column which has several termination dates and what I need to do is to group those dates into years as showing below:

- Less than 1 year
- 1 year
- 2-3 year
- More than 3 years

What is the best formula to use?


Thanks!

 

[sandy666]

use XL2BB to post representative example of source data AND expected result

 

[aRandomHelper]

Try:

=IF(E2-TODAY()>=1095,"More than 3 years",IF(E2-TODAY()>=730,"2-3 year",IF(E2-TODAY()>=365,"1 year","Less than 1 year")))

Put this formula in a new column, change E2 to your date column accordingly. Now you can filter on this new column.

 

[zinah]

Try:

=IF(E2-TODAY()>=1095,"More than 3 years",IF(E2-TODAY()>=730,"2-3 year",IF(E2-TODAY()>=365,"1 year","Less than 1 year")))

Put this formula in a new column, change E2 to your date column accordingly. Now you can filter on this new column.

Thank you for your quick response!
I tried your formula after adding another condition which was: if the cell is empty, then keep it empty and all the results were "Less than 1 year" + empty
Not sure why this happened, any thoughts or suggested amendment to the formula?

Formula:

=IF(CM15="","",IF(CM15-TODAY()>=1095,"More than 3 years",IF(CM15-TODAY()>=730,"2-3 year",IF(CM15-TODAY()>=365,"1 year","Less than 1 year"))))

Date	Formula
1/1/2021​	Less than 1 year​
10/1/2019​	Less than 1 year​
4/1/2020​	Less than 1 year​
11/1/2019​	Less than 1 year​
4/1/2020​	Less than 1 year​
4/1/2019​	Less than 1 year​

 

[Fluff]

With that date you don't have any dates that are 1 year or greater.

+Fluff v2.xlsm
	C	D
14	Date	Formula
15	01/01/2021	Less than 1 year
16	10/01/2019	Less than 1 year
17	04/01/2022	Less than 1 year
18	11/01/2023	1 year
19	04/01/2024	2-3 year
20	04/01/2025	More than 3 years
Sheet2

Cell Formulas
Range	Formula
D15:D20	D15	=IF(C15="","",IF(C15-TODAY()>=1095,"More than 3 years",IF(C15-TODAY()>=730,"2-3 year",IF(C15-TODAY()>=365,"1 year","Less than 1 year"))))

 

[zinah]

With that date you don't have any dates that are 1 year or greater.

+Fluff v2.xlsm
	C	D
14	Date	Formula
15	01/01/2021	Less than 1 year
16	10/01/2019	Less than 1 year
17	04/01/2022	Less than 1 year
18	11/01/2023	1 year
19	04/01/2024	2-3 year
20	04/01/2025	More than 3 years
Sheet2

Cell Formulas
Range	Formula
D15:D20	D15	=IF(C15="","",IF(C15-TODAY()>=1095,"More than 3 years",IF(C15-TODAY()>=730,"2-3 year",IF(C15-TODAY()>=365,"1 year","Less than 1 year"))))

Thank you for your usual help! I know why the formula is not working as I expected, it's because I was not clear in my question. What I want is to set the dates according to past not future:

- Less than 1 year: is any day that's less than 1 year i.e. Jan 5, 2021
- 1 year: is any day that passed 1 year i.e. Jan 15, 2020
- 2 to 3 year: any day that passed 2 to 3 year (during 2019 & 2018

 

[Fluff]

How about

+Fluff v2.xlsm
	C	D
14	Date	Formula
15	15/01/2021	Less than 1 year
16	15/01/2020	Less than 1 year
17	15/01/2019	1 year
18	15/01/2018	2-3 Years
19	15/01/2017	More than 3 years
20	15/01/2016	More than 3 years
Sheet2

Cell Formulas
Range	Formula
D15:D20	D15	=IF(C15="","",IF(EDATE(C15,12*3)<TODAY(),"More than 3 years",IF(EDATE(C15,12*2)<TODAY(),"2-3 Years",IF(EDATE(C15,12)<TODAY(),"1 year","Less than 1 year"))))

 

[zinah]

How about

+Fluff v2.xlsm
	C	D
14	Date	Formula
15	15/01/2021	Less than 1 year
16	15/01/2020	Less than 1 year
17	15/01/2019	1 year
18	15/01/2018	2-3 Years
19	15/01/2017	More than 3 years
20	15/01/2016	More than 3 years
Sheet2

Cell Formulas
Range	Formula
D15:D20	D15	=IF(C15="","",IF(EDATE(C15,12*3)<TODAY(),"More than 3 years",IF(EDATE(C15,12*2)<TODAY(),"2-3 Years",IF(EDATE(C15,12)<TODAY(),"1 year","Less than 1 year"))))

Amazing! that worked perfectly great. Only one hiccup, dates from Feb 2019 to Dec 2019 were labeled under 1 year, so I changed the text to 1 year and less than 2 year to reflect. Unless if you have a solution for that? which I'm fine either way.

Thank you once again!

 

[Fluff]

Glad we could help & thanks for the feedback.