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]

Give this one a go, I've changed the date part of the formula so that it should always run for the correct period based on today's date (the bit in bold).

=IF(DATEDIF(MINIFS($B$2:$B$10,$A$2:$A$10,A2),EOMONTH(WORKDAY(TODAY(),1),-1),"M")<13,"New",IF(COUNTIFS($A$2:$A$10,A2,$D$2:$D$10,"<>closed")=0,"Closed",IF(SUMIFS($C$2:$C$10,$A$2:$A$10,A2)<0,"out","in")))

Effectively it always looks at the previous month based on TODAY(), but if TODAY() is the last weekday of the month then it will advance it to the current month.

 

[Mike820]

you did it once again. Thanks a million.

 

[Mike820]

Give this one a go, I've changed the date part of the formula so that it should always run for the correct period based on today's date (the bit in bold).

=IF(DATEDIF(MINIFS($B$2:$B$10,$A$2:$A$10,A2),EOMONTH(WORKDAY(TODAY(),1),-1),"M")<13,"New",IF(COUNTIFS($A$2:$A$10,A2,$D$2:$D$10,"<>closed")=0,"Closed",IF(SUMIFS($C$2:$C$10,$A$2:$A$10,A2)<0,"out","in")))

Effectively it always looks at the previous month based on TODAY(), but if TODAY() is the last weekday of the month then it will advance it to the current month.

Could you please explain this part of the formula and how it works? ",IF(COUNTIFS($A$2:$A$10,A2,$D$2:$D$10,"<>closed")=0,"Closed".

Thanks,

 

[jasonb75]

It counts the number of rows with the same ID where the tag is anything other than closed.
If the result of that count is 0 then the result is 'closed'. Counts greater than 0 are passed to the next part to see if it is in or out.