Less than 6 month,6M to 1 year,1y-2years - Function With Payment & Receipt Days

[mukeshkatara]

How to get an age-based report in Excel if the client does not have a bill-by-bill report. I need a formula for the first invoice against the payment setup, with the balance indicated as the Following

Less than 6 months
6 months -1 year
1-2 years Query Sheet

MNK-Trading.xlsx
	A	B	C	D	E	F	G
1	MNK TRADING COMPANY
2	1-Apr-21 to 31-Aug-22
3	Date	Particulars	Column1	Vch Type	Vch No.	Debit	Credit
4	01-Apr-21	By	Opening Balance			0	3469074.00
5	08-Apr-21	To	Local Sales	Sales	62	588210.00	0
6	22-Apr-21	By	SBI CC	Receipt	0	0	408476.00
7	27-Apr-21	To	Local Sales	Sales	239	630000.00	0
8	27-Apr-21	By	SBI CC	Receipt	0	0	1000000.00
9	10-May-21	To	Local Sales	Sales	306	691950.00	0
10	19-May-21	By	SBI CC	Receipt	0	0	700000.00
11	10-Jun-21	To	Local Sales	Sales	553	582540.00	0
12	24-Jun-21	To	Local Sales	Sales	727	695520.00	0
13	16-Jul-21	To	Local Sales	Sales	885	492030.00	0
14	17-Jul-21	To	Local Sales	Sales	889	520538.00	0
15	20-Jul-21	To	Local Sales	Sales	900	469508.00	0
16	03-Aug-21	To	Local Sales	Sales	961	435173.00	0
17	21-Aug-21	To	Local Sales	Sales	1075	400838.00	0
18	19-Oct-21	To	Local Sales	Sales	1350	996114.00	0
19	29-Oct-21	To	Local Sales	Sales	1418	719250.00	0
20	12-Nov-21	To	Local Sales	Sales	1450	718410.00	0
21	15-Nov-21	To	Local Sales	Sales	VF/2122/SCR/0011	543900.00	0
22	18-Nov-21	By	SBI CC	Receipt	0	0	500000.00
23	05-Dec-21	To	Local Sales	Sales	VF/2122/SCR/0175	386348.00	0
24	08-Jan-22	To	Local Sales	Sales	VF/2122/SCR/0556	513660.00	0
25	10-Jan-22	To	Local Sales	Sales	VF/2122/SCR/0573	468405.00	0
26	18-Jan-22	By	SBI CC	Receipt	0	0	1000000.00
27	23-Mar-22	By	SBI CC	Receipt	0	0	500000.00
28	31-Mar-22	To	TCS COLLECTED ON SALE (0.10%)-206(1H)	Debit Note(Sales)	DN/2021-22/47	4852.00	0
29	03-Apr-22	To	Local Sales	Sales(BHU)	VF/2223/BHU/0010	1140048.00	0
30	04-Apr-22	To	Local Sales	Sales(BHU)	VF/2223/BHU/0016	1258740.00	0
31	05-Apr-22	To	Local Sales	Sales(BHU)	VF/2223/BHU/0020	1413720.00	0
32	06-Apr-22	To	Local Sales	Sales(BHU)	VF/2223/BHU/0021	785925.00	0
33	07-Apr-22	By	SBI CC	Receipt	0	0	200000.00
34	17-Apr-22	To	Local Sales	Sales(BHU)	VF/2223/BHU/0067	803565.00	0
35	30-Apr-22	To	TCS COLLECTED ON SALE (0.10%)-206(1H)	Debit Note(Sales)	DN/2022-23/02	402.00	0
36	08-May-22	To	Local Sales	Sales	VF/2223/SCR/0143	1226988.00	0
37	21-May-22	To	Local Sales	Sales	VF/2223/SCR/0176	887086.00	0
38	31-May-22	By	SBI CC	Receipt	0	0	2500000.00
39	05-Jun-22	To	Local Sales	Sales	VF/2223/SCR/0242	1187760.00	0
40	07-Jun-22	To	Local Sales	Sales	VF/2223/SCR/0258	1340976.00	0
41	07-Jun-22	To	Local Sales	Sales(BHU)	VF/2223/BHU/0078	1628718.00	0
42	16-Jun-22	To	TCS COLLECTED ON SALE (0.10%)-206(1H)	Debit Note(Sales)	DN/2022-23/03	6272.00	0
43	01-Jul-22	To	Local Sales	Sales	VF/2223/SCR/0418	1513418.00	0
44	07-Jul-22	To	Local Sales	Sales	VF/2223/SCR/0440	1729350.00	0
45	08-Jul-22	By	SBI CC	Receipt	0	0	2000000.00
46	08-Jul-22	To	Local Sales	Sales(BHU)	VF/2223/BHU/0093	1367626.00	0
47	09-Jul-22	To	Local Sales	Sales	VF/2223/SCR/0443	751080.00	0
48	14-Jul-22	By	SBI CC	Receipt	0	0	2000000.00
49	28-Jul-22	To	Local Sales	Sales	VF/2223/SCR/0508	783558.00	0
50	03-Aug-22	By	SBI CC	Receipt	0	0	2000000.00
51	19-Aug-22	To	Local Sales	Sales(BHU)	VF/2223/BHU/0110	671884.00	0
PARKASH TRADING COMPANY