How to calculate the number of days between two dates dependent on criteria

[JENKELLY]

Hi
I have the following data (this is an example - there are approx 45000 rows in the original doc which will only increase with time!):

Cust No	End of Period Date	Carried Forward Balance
JH3	2019-01-07	1032.06
JH3	2018-12-07	1130.82
JH3	2018-12-07	1732.06
JH3	2018-12-07	1132.06
JH3	2018-11-12	1230.82
RB5	2019-01-07	1182.76
RB5	2018-12-07	1082.76
RB5	2018-12-07	982.76
RB5	2018-12-07	40.14
RB5	2018-11-12	611.45
RB5	2018-10-03	-1.17
RB5	2018-09-04	570.14
RB5	2018-08-14	40.14
RB5	2018-07-04	-1.17
YT6	2019-01-07	1020.53
YT6	2018-12-07	1020.53
YT6	2018-12-07	1020.53
YT6	2018-12-07	510.25
YT6	2018-11-12	1020.53
YT6	2018-10-03	510.25
YT6	2018-09-04	-0.03
YT6	2018-08-14	510.25
YT6	2018-07-04	-0.03
YT6	2018-06-05	-0.03

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I am trying to find the length of time in days that the customer has been in arrears from the most recent end of period date - i.e. back to where their account goes into credit (if it does) or until their account was opened if it has always been in debt. I'm only interested in the current instance of debt (i.e. it doesn't matter if they have been in arrears before) and would like the data to look like this:

Cust No	Days in arrears
JH3	56
RB5	56
YT6	96

<colgroup><col><col></colgroup><tbody>
</tbody>

The data is exported out and refreshed, so needs to be able to cope with this.
Any help is much appreciated!

Thank you

Jenny

 

[steve the fish]

Hi. Before anyone can really answer where does 56 come from? Im struggling to see how the first two customers can both produce the same number based on your data and explanation.

 

[AlanY]

try this, revise range to suit

Book1
	A	B	C	D	E	F
1	Cust No	End of Period Date	Carried Forward Balance
2	JH3	07/01/2019	1032.06		JH3	56
3	JH3	07/12/2018	1130.82		RB5	56
4	JH3	07/12/2018	1732.06		YT6	96
5	JH3	07/12/2018	1132.06
6	JH3	12/11/2018	1230.82
7	RB5	07/01/2019	1182.76
8	RB5	07/12/2018	1082.76
9	RB5	07/12/2018	982.76
10	RB5	07/12/2018	40.14
11	RB5	12/11/2018	611.45
12	RB5	03/10/2018	-1.17
13	RB5	04/09/2018	570.14
14	RB5	14/08/2018	40.14
15	RB5	04/07/2018	-1.17
16	YT6	07/01/2019	1020.53
17	YT6	07/12/2018	1020.53
18	YT6	07/12/2018	1020.53
19	YT6	07/12/2018	510.25
20	YT6	12/11/2018	1020.53
21	YT6	03/10/2018	510.25
22	YT6	04/09/2018	-0.03
23	YT6	14/08/2018	510.25
24	YT6	04/07/2018	-0.03
25	YT6	05/06/2018	-0.03
Sheet3

Cell Formulas
Range	Formula
F2	{=MAXIFS($B$2:$B$25,$A$2:$A$25,E2)-IFERROR(INDEX($B$2:$B$25,MATCH(TRUE,IF($A$2:$A$25=E2,$C$2:$C$25)<0,0)-1),MINIFS($B$2:$B$25,$A$2:$A$25,E2))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JENKELLY]

Hi
Thanks for responding so quickly.

The first customer (JH3) has always been in debt, so the days are calculated by 2019-01-07 take away 2018-11-12 is 56 days.

The second customer (RB5) has only been in debt since 2018-11-12. Prior to that, their account was in credit. I just need to know the period they are in debt so, again, 2018-11-12 taken away from 2019-01-07 is 56 days.

Does that make sense?

Thanks
Jenny

 

[JENKELLY]

Hi AlanY,

That is pretty fricking awesome! Thank you very much - works like a charm! It's been driving me bonkers for some time now. I have no idea how that works, but it's great!

Wishing I had your brain...

Jenny

 

[AlanY]

you're welcome.

and, you don't.

 

[JENKELLY]

Hi AlanY
I've just spotted an anomaly...which is my fault as I didn't include this in the original data. Where a customer is not in arrears currently (i.e. showing a negative amount on 07/01/2019), the array produces an incorrect result. Is there a way round this?
Thanks again.
Jenny

 

[AlanY]

modified to suit

Book1
	A	B	C	D	E	F
1	Cust No	End of Period Date	Carried Forward Balance
2	JH3	07/01/2019	1032.06		JH3	56
3	JH3	07/12/2018	1130.82		RB5	56
4	JH3	07/12/2018	1732.06		YT6	96
5	JH3	07/12/2018	1132.06		AY1	---
6	JH3	12/11/2018	1230.82
7	RB5	07/01/2019	1182.76
8	RB5	07/12/2018	1082.76
9	RB5	07/12/2018	982.76
10	RB5	07/12/2018	40.14
11	RB5	12/11/2018	611.45
12	RB5	03/10/2018	-1.17
13	RB5	04/09/2018	570.14
14	RB5	14/08/2018	40.14
15	RB5	04/07/2018	-1.17
16	YT6	07/01/2019	1020.53
17	YT6	07/12/2018	1020.53
18	YT6	07/12/2018	1020.53
19	YT6	07/12/2018	510.25
20	YT6	12/11/2018	1020.53
21	YT6	03/10/2018	510.25
22	YT6	04/09/2018	-0.03
23	YT6	14/08/2018	510.25
24	YT6	04/07/2018	-0.03
25	YT6	05/06/2018	-0.03
26	AY1	07/01/2019	-1032.06
27	AY1	07/12/2018	-1130.82
28	AY1	07/12/2018	1732.06
Sheet3

Cell Formulas
Range	Formula
F2	{=IF(INDEX($C$2:$C$28,MATCH(E2&"|"&MAXIFS($B$2:$B$28,$A$2:$A$28,E2),$A$2:$A$28&"|"&$B$2:$B$28,0))<0,"---",MAXIFS($B$2:$B$28,$A$2:$A$28,E2)-IFERROR(INDEX($B$2:$B$28,MATCH(TRUE,IF($A$2:$A$28=E2,$C$2:$C$28)<0,0)-1),MINIFS($B$2:$B$28,$A$2:$A$28,E2)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JENKELLY]

That's excellent. Thank you so much for your help. I will endeavour to try and understand what it all means!!! Jenny