If statement with date range

[Mike820]

Hi, I currently have data organized similar to below. I'm struggling to write a formula. I'm trying to do the following: if the oldest date tied to an ID is within the last 13 months then return "New" if the oldest date is older than 13 months then sum all the amounts for that relationship ID and if it's a positive number then return "In" if negative return "out".

ID	Date	Amount	Formula column
1	12/1/2019	1000	New
1	5/2/2019	-2500	New
2	11/5/2017	5000	In
2	2/2019	-3000	In
3	3/2/2016	-5000	Out

 

[jasonb75]

This should do it

Book123.xlsm
	A	B	C	D
1	ID	Date	Amount	Formula column
2	1	01/12/2019	1000	New
3	1	02/05/2019	-2500	New
4	2	05/11/2017	5000	in
5	2	Feb-19	-3000	in
6	3	02/03/2016	-5000	out
Sheet10

Cell Formulas
Range	Formula
D2:D6	D2	=IF(DATEDIF(MAXIFS($B$2:$B$6,$A$2:$A$6,A2),TODAY(),"M")<13,"New",IF(SUMIFS($C$2:$C$6,$A$2:$A$6,A2)<0,"out","in"))

 

[Mike820]

Thank you, this seems like what I'm looking for but what if I'd like to set a date myself instead of using the Today function. For example, "New" would be any account February 2018 and newer?

 

[jasonb75]

In that case, just replace TODAY() with a reference to a cell that contains your set date.
Remember that if you only enter month and year that it will evaluate from the first day of the month, not the last.

 

[Mike820]

In that case, just replace TODAY() with a reference to a cell that contains your set date.
Remember that if you only enter month and year that it will evaluate from the first day of the month, not the last.

Thank you for all your help, the last formula worked flawlessly, I had to change the formula to MINIFS for what I needed. I have an additional caveat I'd like to include in the previous formula you provided, wondering if you could help.

1) Creation of an additional segment called "Closed" as shown in ID 5 below: if all Tags for an ID is "Closed" then return "Closed". The formula you provided for "New" will always take precedent shown in ID 4 below but if it's not "Closed" or "New" then it follows same rules for "In" or "Out".


Thanks

ID	Date	Amount	Tag	Formula column
1	12/1/2019	1000	In	New
1	5/2/2019	-2500	out	New
2	11/5/2017	5000	In	In
2	2/2019	-3000	out	In
3	3/2/2016	-5000	Out	Out
4	6/5/2017	-1000	Closed	New
4	3/1/2019	200	Closed	New
5	5/2017	500	Closed	Closed
5	2/2017	-1000	Closed	Closed

 

[jasonb75]

What exactly are you using for formula and criteria (i.e. set date for new) with the example above?

I can't see why ID 4 is new and ID 2 is not, changing MAXIFS to MINIFS doesn't appear to make any difference to the results.

 

[Mike820]

What exactly are you using for formula and criteria (i.e. set date for new) with the example above?

I can't see why ID 4 is new and ID 2 is not, changing MAXIFS to MINIFS doesn't appear to make any difference to the results.

Providing some additional background: each month a new report is generated and I need to insert the formula to generate these 4 segments (New, Closed, In, and Out). So let’s say the report was generated month end Mar 2020. I’d want to tag any IDs as New if the oldest (earliest) date was within the last 13 months from Mar 2020. In the April report, I’d want to tag anything as New if the oldest Date within an ID was within 13 months from April 2020 etc. the formula was returning an error using MAXif in certain case but it worked when I changed it to Minif and practically thinking about it, Min seems like the correct way I should be approaching it.

 

[Mike820]

In the March report I'm currently using the formula below. I changed MAXIFS to MINIFS and also the date for whatever month I'm working in. So this would return "New" any ID's where the oldest (earliest) date was within the last 13 months from March 31 2020, so basically any dates from March 31 2020 to March 1 2019.

=IF(DATEDIF(MINIFS($B$2:$B$6,$A$2:$A$6,A2),"31 Mar 2020","M")<13,"New",IF(SUMIFS($C$2:$C$6,$A$2:$A$6,A2)<0,"out","in"))

 

[jasonb75]

So why is ID 4 'New' in the example?

Based on the description that you have provided it should either be 'Out' or 'Closed'

 

[Mike820]

So why is ID 4 'New' in the example?

Based on the description that you have provided it should either be 'Out' or 'Closed'

You are correct. This should be tagged as Closed. I meant to have the date as 6/5/2019 not 6/5/2017 for this example of show it would be tagged as New. But you are correct in ID 4 this would be Closed because the oldest date is 6/5/2017 outside of the 13 month window and all “tags” are “Closed” for this ID.