Pick DATE where bills are fully received

[myexcel202424]

I need DATE in column G where the "Bill Amount" (Column D), Bill ID wise, is received in full.

Example: Amount of Bill ID 101 is received partially on 2.5.24 and fully received on 3.5.2024. So here Bill ID no .101 is fully received on 3.5.24.

Unable to attach the Excel sheet/mini sheet, so, I have attached the pic.

Please guide.
Thanks

 

[JamesCanale]

Here's how to do it with an accursed helper column. In later versions of excel, the helper column could be built in easily enough with an array formula and some nice MMULT.

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H
1	Slno	Bill ID	date	Bill Amount	Payment Rcvd	Balance	Running payments	Date that bill was fully paid
2	1	101	1-May	$10,000		-$10,000	$0	3-May
3	2	102	2-May	$12,000		-$22,000	$0	9-May
4	3		2-May		$5,000	-$17,000	$5,000	9-May
5	4	103	3-May	$6,500		-$23,500	$5,000	9-May
6	5		3-May		$6,000	-$17,500	$11,000	9-May
7	6	104	4-May	$8,200		-$25,700	$11,000	9-May
8	7	105	5-May	$21,000		-$46,700	$11,000	9-May
9	8	106	6-May	$32,300		-$79,000	$11,000	11-May
10	9	107	7-May	$2,800		-$81,800	$11,000	11-May
11	10	108	7-May	$600		-$82,400	$11,000	Open
12	11	109	8-May	$15,000		-$97,400	$11,000	Open
13	12		9-May		$78,000	-$19,400	$89,000	Open
14	13	110	10-May	$1,100		-$20,500	$89,000	Open
15	14	111	11-May	$39,000		-$59,500	$89,000	Open
16	15	112	11-May	$1,700		-$61,200	$89,000	Open
17	16		11-May		$4,000	-$57,200	$93,000	Open
Sheet7

Cell Formulas
Range	Formula
F2	F2	=-D2
G2	G2	=E2
H2:H17	H2	=IFERROR(INDEX($C$2:$C$17,1+MATCH(SUM(D$2:D2),$G$2:$G$17,1)),"Open")
F3:F17	F3	=F2-D3+E3
G3:G17	G3	=G2+E3

 

[myexcel202424]

Here's how to do it with an accursed helper column. In later versions of excel, the helper column could be built in easily enough with an array formula and some nice MMULT.

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H
1	Slno	Bill ID	date	Bill Amount	Payment Rcvd	Balance	Running payments	Date that bill was fully paid
2	1	101	1-May	$10,000		-$10,000	$0	3-May
3	2	102	2-May	$12,000		-$22,000	$0	9-May
4	3		2-May		$5,000	-$17,000	$5,000	9-May
5	4	103	3-May	$6,500		-$23,500	$5,000	9-May
6	5		3-May		$6,000	-$17,500	$11,000	9-May
7	6	104	4-May	$8,200		-$25,700	$11,000	9-May
8	7	105	5-May	$21,000		-$46,700	$11,000	9-May
9	8	106	6-May	$32,300		-$79,000	$11,000	11-May
10	9	107	7-May	$2,800		-$81,800	$11,000	11-May
11	10	108	7-May	$600		-$82,400	$11,000	Open
12	11	109	8-May	$15,000		-$97,400	$11,000	Open
13	12		9-May		$78,000	-$19,400	$89,000	Open
14	13	110	10-May	$1,100		-$20,500	$89,000	Open
15	14	111	11-May	$39,000		-$59,500	$89,000	Open
16	15	112	11-May	$1,700		-$61,200	$89,000	Open
17	16		11-May		$4,000	-$57,200	$93,000	Open
Sheet7

Cell Formulas
Range	Formula
F2	F2	=-D2
G2	G2	=E2
H2:H17	H2	=IFERROR(INDEX($C$2:$C$17,1+MATCH(SUM(D$2:D2),$G$2:$G$17,1)),"Open")
F3:F17	F3	=F2-D3+E3
G3:G17	G3	=G2+E3

Thank you very much dear. Will try and revert.

 

[myexcel202424]

Thank you very much dear. Will try and revert.

Hey James !!

Good afternoon

Your solution made my work easy. Thanks again.

Can you please help me with one more problem in the same sheet.

1. How to get the DATE of the partially cleared BILLs? Like out of $ 32300 againt Bill ID 106 $ 31300 were received on 9.5.24 and full amount is received on 9.5.24.

2. I need to add $ 1 to the PAYMENT RECEIVED for excel to calculate it under fully received. (If I change the figure of 78000 to 79000 at cell number E13, still excel returns the value (DATE of fully paid) as 11.5.24 and not 9.5.24 for BILL ID 106.

Kindly help