Need a formula that works out three outcomes owing to the date’s points are scored in 6 months

[Leicester City Fox]

Hi All

I need a formula that will give me results below column D the Outcome Action.

There will be 3 outcomes the formula works out and enters the result in column D:

1. “Points Still Live”, still within six months of the Start date (Column A) and (Column C) the point removed date. Basically, six months has not passed.
   
   
2. “Automatic Ban”, if the point total 15 or are 15 point within 6 month it states Automatic Ban.

1. “Points Spent (6mths)”, the point is lower than 15 point after 6moths date (column C).

( A) Date Point Given​	(B) Points​	(C) Point Removed Date (after 6months)​	(D) Outcome Action​
05/02/2020	8​	01/07/2020	Points Still Live​
05/01/2020	15​	05/07/2020	Automatic Ban​
17/07/2019	5​	17/01/2020	Points Spent (6mths)​
14/02/2020	4​	14/08/2020	Point Still Live​

If anybody can think of the best and easiest formula to get me the result in Colum D I would be very grateful.

Many Thanks



The Leicester Fox

 

[etaf]

i think this should work
=IF(AND(DATE(YEAR(A3),MONTH(A3)+6,DAY(A3)) > =C3,B3 < 15),"points still live",IF(AND(DATE(YEAR(A3),MONTH(A3)+6,DAY(A3)) < = C3,B3 < 15),"Points spent (6mth)",IF(AND(DATE(YEAR(A3),MONTH(A3)+6,DAY(A3)) > = C3,B3 > = 15),"Automatic Ban","")))

 

[etaf]

(DATE(YEAR(A4),MONTH(A4)+6,DAY(A4)) > = C4 ) = less than 6mths
(DATE(YEAR(A4),MONTH(A4)+6,DAY(A4)) < = C4 )= greater than 6mths

Points still live = less than 6 mths AND less then 15 points
points spent (6mth) = more than 6mth AND less than 15 points
Automatic ban = less than 6 mths AND more then or equal to 15 points
otherwise blank

 

[Leicester City Fox]

i think this should work
=IF(AND(DATE(YEAR(A3),MONTH(A3)+6,DAY(A3)) > =C3,B3 < 15),"points still live",IF(AND(DATE(YEAR(A3),MONTH(A3)+6,DAY(A3)) < = C3,B3 < 15),"Points spent (6mth)",IF(AND(DATE(YEAR(A3),MONTH(A3)+6,DAY(A3)) > = C3,B3 > = 15),"Automatic Ban","")))

Hi etaf

Good Morning

The formula appears not to be working full as it is giving me “Automatic Ban” result for all outcomes in Colum D and not the correct results below.

Also, my apologies I forget to add the provider Column so it should only add up relative dates for relative providers their points so the example Point so Provider A would (8 +8 = 16 ) giving “Automatic Ban “ As the point all fall within 6 months period toting up

Provider​	( A) Date Point Given​	(B) Points​	(C) Point Removed Date (after 6months)​	(D) Outcome Action​
A	05/02/2020	8​	01/07/2020	Points Still Live​
B	05/01/2020	15​	05/07/2020	Automatic Ban​
C	17/07/2019	5​	17/01/2020	Points Spent (6mths)​
D	14/02/2020	4​	14/08/2020	Point Still Live​
A	10/02/2020	8​	10/07/2020	Automatic Ban​

Do you have any ideas to make it work?

Many Thanks

Leicester Fox

 

[Leicester City Fox]

Hi etaf

Good Morning

The formula appears not to be working full as it is giving me “Automatic Ban” result for all outcomes in Colum D and not the correct results below.

Also, my apologies I forget to add the provider Column so it should only add up relative dates for relative providers their points so the example Point so Provider A would (8 +8 = 16 ) giving “Automatic Ban “ As the point all fall within 6 months period toting up

Provider​	( A) Date Point Given​	(B) Points​	(C) Point Removed Date (after 6months)​	(D) Outcome Action​
A	05/02/2020	8​	01/07/2020	Points Still Live​
B	05/01/2020	15​	05/07/2020	Automatic Ban​
C	17/07/2019	5​	17/01/2020	Points Spent (6mths)​
D	14/02/2020	4​	14/08/2020	Point Still Live​
A	10/02/2020	8​	10/07/2020	Automatic Ban​

Do you have any ideas to make it work?

Many Thanks

Leicester Fox

Hi etaf


I forgot to mention:


I need the “Points Live to change to “Point spent” (Column D) Once the Point Removed Date (Column C) has passes it automatically updates owing to the current date in time.


Can this be incorporated into the formula?



Thanks


Leicester Fox

 

[etaf]

What date , I assume the first date found for a provider
so your example A = 16 points , also the date would be 05/02/2020. what happens if the points are over 6 mths apart ?
1/1/19 8 points
2/6/19 8 points
perhaps some examples of possible combinations and the outcome