Help with date clusters please

[chef]

I would really appreciate any help and guidance around this and hoping possible.

I have a series of contract end dates in column F and am trying to cluster as follows
Using an end of month date in Cell H1 eg 31Oct 22 as fixed point (that I can change each month) I have been looking at nesting lookup/ if function with little success

Id like to show a category for each date held in column F and record in Column G and whether they are

1-3 mths
4-6 mths
7-12 mths
Over 1 year
Over 2 years

Im wracking my brain trying to figure and hoping maybe an easy solution either with formula or VBA
Many thanks

 

[Peter_SSs]

Perhaps some sample data and the expected results (entered manually) with XL2BB would help clarify exactly what you have and what you are trying to achieve.

 

[chef]

Thank you for such a prompt response.
I am having problems with XL2BB add in and maybe blocked by work admin adding this ?
Ive tried to show outline example and apologies if not clear or enough to progress

Col F Col G Col H End Date Category 31-Oct-22 31/08/2022 1-3 mths 31/05/2022 4-6 mths 31/03/2022 7-12 mths 31/07/2021 Over 1 year 30/09/2020 Over 2 years
		​
​
​
​
​
​

 

[Peter_SSs]

I am having problems with XL2BB add in and maybe blocked by work admin adding this ?

In that case in future try a direct copy/paste from Excel and explain what range is shown. For example: F1:H6

End Date	Category	31-Oct-22​
31/08/2022​	1-3 months
31/05/2022​	4-6 months
31/03/2022​	7-12 months
31/07/2021​	Over 1 year
30/09/2020​	Over 2 years

See if this is what you want.

22 10 29.xlsm
	F	G	H
1	End Date	Category	31-Oct-22
2	31/08/2022	1-3 months
3	31/05/2022	4-6 months
4	31/03/2022	7-12 months
5	31/07/2021	Over 1 year
6	30/09/2020	Over 2 years
Group Dates

Cell Formulas
Range	Formula
G2:G6	G2	=IF(EDATE(H$1,-24)>F2,"Over 2 years",IF(EDATE(H$1,-12)>F2,"Over 1 year",IF(EDATE(H$1,-6)>F2,"7-12 months",IF(EDATE(H$1,-3)>F2,"4-6 months","1-3 months"))))

 

[chef]

Thank you so so much for your help and patience.
This is great and will save a lot of manual work and really appreciate the help.
I would never have figured that out.
regards

 

[Peter_SSs]

You're welcome. Glad it helped.