Calculating the number of days to payment

[Vegas01]

Hi everyone, seeing if anyone can help with a solution to my problem. I need to work out on the invoice how many days it took to get paid. the results we are looking for are in column O. Thanks in advance

Test data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	Type			Inv Number	Inv Amt	DR/CR	Pay Amt	DR/CR 2	Acc Balance	DR/CR 3	Creditors No	Week invoice received		Result that is needed
2	1/06/2021	Inv	100049			1,047.90	CR			1,047.90	CR	Q10400	49
3	11/05/2021	Inv	712928			33.00	CR			33.00	CR	Q10420	46		2
4	12/05/2021	O/Bal								33.00	CR	Q10420	46		1
5	13/05/2021	Pay	002462	layerEFT				33.00		-		Q10420	46	2
6	3/02/2021	Inv	096462			243.10	CR			243.10	CR	Q10915	32		1
7	3/02/2021	O/Bal								243.10	CR	Q10915	32
8	4/02/2021	Pay	002554	layerEFT				243.10		-		Q10915	32	1
9	19/02/2021	Inv	009877			1,219.63	CR			1,219.63	CR	Q10915	34		6
10	24/02/2021	O/Bal								1,219.63	CR	Q10915	35
11	25/02/2021	Pay	002560	layerEFT				1,219.63		-		Q10915	35	6
12	12/01/2021	Inv	009355			35.00	CR			35.00	CR	Q11000	29		2
13	13/01/2021	O/Bal								35.00	CR	Q11000	29
14	14/01/2021	Pay	002548	layerEFT				35.00		-		Q11000	29	2
15	12/01/2021	Inv	313522			84.32	CR			84.32	CR	Q30002	29		2
16	13/01/2021	O/Bal								84.32	CR	Q30002	29
17	14/01/2021	Pay	002548	layerEFT				84.32		-		Q30002	29	2
18	14/01/2021	Inv	318149			183.94	CR			183.94	CR	Q30002	29		7
19	20/01/2021	O/Bal								183.94	CR	Q30002	30
20	21/01/2021	Pay	002550	layerEFT				183.94		-		Q30002	30	7
21	31/05/2021	Inv	400550			362.24	CR			362.24	CR	Q30002	49
22	10/03/2021	Inv	139388			1,346.40	CR			1,346.40	CR	Q30003	37		1
23	10/03/2021	O/Bal								1,346.40	CR	Q30003	37
24	11/03/2021	Pay	002564	layerEFT				1,346.40		-		Q30003	37	1
25	23/12/2020	Inv	001011			540.48	CR			11,216.12		Q30011	26		7
26	23/12/2020	Inv	001197			257.34	CR			10,958.78		Q30011	26		7
27	23/12/2020	Inv	045664			458.69	CR			10,500.09		Q30011	26		7
28	23/12/2020	Inv	045665			613.26	CR			9,886.83		Q30011	26		7
29	23/12/2020	Inv	045666			140.52	CR			9,746.31		Q30011	26		7
30	23/12/2020	Inv	045667			2,385.07	CR			7,361.24		Q30011	26		7
31	23/12/2020	Inv	045675			1,548.50	CR			5,812.74		Q30011	26		7
32	23/12/2020	Inv	045676			413.40	CR			5,399.34		Q30011	26		7
33	23/12/2020	Inv	045677			831.08	CR			4,568.26		Q30011	26		7
34	23/12/2020	Inv	045678			716.98	CR			3,851.28		Q30011	26		7
35	23/12/2020	Inv	045679			779.73	CR			3,071.55		Q30011	26		7
36	23/12/2020	Inv	045680			264.56	CR			2,806.99		Q30011	26		7
37	23/12/2020	Inv	045682			1,166.09	CR			1,640.90		Q30011	26		7
38	23/12/2020	Inv	045683			215.19	CR			1,425.71		Q30011	26		7
39	23/12/2020	Inv	045684			196.56	CR			1,229.15		Q30011	26		7
40	23/12/2020	Inv	045685			694.38	CR			534.77		Q30011	26		7
41	23/12/2020	Inv	045686			534.77	CR			-		Q30011	26		7
42	30/12/2020	Pay	015213	PAYMENT				87.42	CR	87.42	CR	Q30011	27	7
43	30/12/2020	Pay	015221	PAYMENT				11,844.02		11,756.60		Q30011	27
44	5/01/2021	Inv	037914			97.00	CR			-		Q30011	28		1
45	6/01/2021	Pay	015233	PAYMENT				97.00		97.00		Q30011	28	1
46	11/01/2021	Inv	045687			715.64	CR			3,651.35		Q30011	29		2
47	11/01/2021	Inv	045689			669.77	CR			2,981.58		Q30011	29		2
48	11/01/2021	Inv	045691			99.14	CR			2,882.44		Q30011	29		2
49	11/01/2021	Inv	045692			646.69	CR			2,235.75		Q30011	29		2
50	11/01/2021	Inv	045693			1,466.81	CR			768.94		Q30011	29		2
51	11/01/2021	Inv	045694			120.27	CR			648.67		Q30011	29		2
52	11/01/2021	Inv	045696			345.84	CR			302.83		Q30011	29		2
53	11/01/2021	Inv	045698			302.83	CR			-		Q30011	29		2
54	12/01/2021	Inv	001080			168.38	CR			4,366.99		Q30011	29		1
55	13/01/2021	Pay	015244	PAYMENT				4,535.37		4,535.37		Q30011	29	2
56	18/01/2021	Inv	014160			2,529.16	CR			5,543.11		Q30011	30		2
57	18/01/2021	Inv	045607			279.69	CR			4,341.86		Q30011	30		2
58	18/01/2021	Inv	045663			149.49	CR			4,192.37		Q30011	30		2
59	18/01/2021	Inv	056874			509.74	CR			629.26		Q30011	30		2
60	18/01/2021	Inv	070325			509.74	CR			119.52		Q30011	30		2
61	18/01/2021	Inv	070361			119.52	CR			-		Q30011	30		2
62	19/01/2021	Inv	037902			921.56	CR			4,621.55		Q30011	30		1
63	19/01/2021	Inv	045690			1,266.93	CR			2,925.44		Q30011	30		1
64	19/01/2021	Inv	045697			1,443.58	CR			1,481.86		Q30011	30		1
65	19/01/2021	Inv	045700			342.86	CR			1,139.00		Q30011	30		1
66	20/01/2021	Pay	015274	PAYMENT				8,072.27		8,072.27		Q30011	30	2
Sheet1

Cell Formulas
Range	Formula
M2:M66	M2	=VLOOKUP(A2,'[QLD - Payment time report - Creditors.xlsx]Daily Breakdown'!A:D,3,FALSE)
N2:N66	N2	=IF(B2="Pay",IFERROR(A2-INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A1)/(($L$1:$L1=L1)*($B$1:$B1="Inv")*(ROW($H$1:$H1)>MAX((ROW($H$1:$H1)*($H$1:$H1>0))))),1)),""),"")
O9,O15,O12	O9	=+A11-A9

 

[kvsrinivasamurthy]

In R3 then copy down

=IF($B3="Inv",IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A4:$A$67)/(($B4:$B$67="Pay")*($H4:$H$67=$F3)),1))-$A3,""),"")

 

[Vegas01]

In R3 then copy down

=IF($B3="Inv",IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A4:$A$67)/(($B4:$B$67="Pay")*($H4:$H$67=$F3)),1))-$A3,""),"")

Thanks a lot for that, that works where there is 1 invoice but does not worked when a payment is paying a number of invoices. ie in rows 25 to 41.

Do you think there is a solution or is this problem impossible to solve? Thanks in advance for your help.

 

[kvsrinivasamurthy]

Try this . Working Ok.
In R3 and down.

=IF(AND($B3="Inv",INDEX($A:$A,AGGREGATE(15,6,ROW($A4:$A$67)/(($B4:$B$67="Pay")),1))>=$A3),IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A4:$A$67)/(($B4:$B$67="Pay")),1))-$A3,""),"")

 

[Vegas01]

Thanks for that it now works, Stay safe.

 

[kvsrinivasamurthy]

Thanks for feedback