Lapsed Customers

[RockNGoalStar]

Hi All,

I am trying to work out a formula that will show me whether a customer's last order with me was within the last 3 months. I have a column with dates of their last order (F).

So, essentially I would like to ask IF this date occurred more than 3 months ago from TODAY's date then then the customer is Lapsed. if it was within the last 3 months then the customer is current.

If someone could help me with that, then I would be really grateful!

As an additional complication, but not wholly necessary if too much trouble, but would it be possible to work out customers who hadn't ordered for even longer, e.g. 6 months, and they would be Super Lapsed.

Many thanks in advance

 

[Fluff]

Sounds like you need the Maxifs function, but can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Automatrix]

asdf.xlsx
	A	B
1	Order date	Status
2	01-09-2022	Not lapsed
3	01-06-2022	Lapsed
4	01-01-2022	Super lapsed
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=IF((TODAY()-[@[Order date]])>180,"Super lapsed",IF((TODAY()-[@[Order date]])>90,"Lapsed","Not lapsed"))

 

[RockNGoalStar]

asdf.xlsx
	A	B
1	Order date	Status
2	01-09-2022	Not lapsed
3	01-06-2022	Lapsed
4	01-01-2022	Super lapsed
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=IF((TODAY()-[@[Order date]])>180,"Super lapsed",IF((TODAY()-[@[Order date]])>90,"Lapsed","Not lapsed"))

Hi and many thanks for this... you smashed it!

Saved me tons of time. Thanks again!

 

[RockNGoalStar]

Sounds like you need the Maxifs function, but can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks for taking the time to reply and for the info

 

[Fluff]

It's always best to supply data, otherwise we are all just guessing as to your data layout.

 

[Automatrix]

Thanks for taking the time to reply and for the info

My pleasure. Keep in mind that this solution does not strictly use 3 and 6 months, but 90 and 180 days.