Please Help - Criteria wording - Three (3) date ranges in for advance filter.

[joach]

Hi,

I'm wanting to use advance filters to filter my data set.

I am wanting to follow up with the customer 8 months, 20 months and 56 months after the purchase date, but if the date is equal to or more than 12, 24 and 60 months from the purchase date I don't want to see the data in the new sheet.

In written terms I want to see the following data however I don't know how to type it in an Excel format / formulas.

follow up
1 year	if todays date is between 8 months and 12 months from the purchase date
2 year	if todays date is between 20 months and 24 months from the purchase date
5 year	if todays date is between 56 months and 60 months from the purchase date

Could you please help me with the formatting / formulas for the above criteria.

In addition to this once the filtered data is on the new sheet 'follow up' I want to use conditional formatting with a colour gradient to show the following - green being the lower number (i.e 8 months), yellow being the mid point (i.e 10 months) and red being the deadline (i.e 12months).

If you could please help with the above that would be amazing.

If you need anything else please let me know.

 

[Alex Blakenburg]

I can't get exactly what you want see if this is close enough.
If this doesn't work for you, you might need to put it out there as a separate thread.

20220720 VBA Follow up Purchase Date Anniv joach v04.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Order Number	Address	Street	Suburb	Description	Estimated cost	Lodged date	Customer name	CSO	purchase type	Purchase date		Year Frac Check Column
2	2		B	B	XXXXXX	100	xx/xx/xx	XXXX	YYYYY	paypal	20/09/2017		10.2
3	133									debit card	31/07/2021		11.86666667
4	100									debit card	20/08/2021		11.2
5	101									debit card	20/09/2021		10.2
6	102									debit card	26/10/2021		9
7	103									debit card	20/11/2021		8.2
Follow Up 12 21 57 Months

Cell Formulas
Range	Formula
M2:M7	M2	=MOD(YEARFRAC(K2,TODAY()),1)*12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2:K7	Expression	=(MOD(YEARFRAC($K2,TODAY()),1)*12)>11	text	YES
K2:K7	Expression	=(MOD(YEARFRAC($K2,TODAY()),1)*12)>9	text	YES
K2:K7	Expression	=(MOD(YEARFRAC($K2,TODAY()),1)*12)<=9	text	YES