Need Help in Dates

[Santosh080]

Hello Friends,

I have a excel data like below,

Date	Debit	Credit
12-02-2011	9,101.00
31-12-2011		65,000.00
18-01-2012		2,000.00
24-02-2012		8,400.00
02-03-2012		4,400.00
30-03-2012		5,000.00
08-10-2012		5,000.00
03-12-2012		5,000.00
24-01-2013	19,372.00
16-05-2013		15,000.00
06-06-2013		10,000.00
02-07-2013		16,000.00
12-08-2013		13,000.00
09-12-2013		4,000.00
23-01-2014	18,870.00
28-01-2014		7,000.00
03-03-2014		8,000.00

Here i want this type result,

Dates	Debit	Credit
12-02-2011	9101	0
28-02-2011	0	0
31-03-2011	0	0
30-04-2011	0	0
31-05-2011	0	0
30-06-2011	0	0
31-07-2011	0	0
31-08-2011	0	0
30-09-2011	0	0
31-10-2011	0	0
30-09-2011	0	0
31-10-2011	0	0
30-11-2011	0	0
31-12-2011	0	65000
18-01-2012	0	2000
31-01-2012	0	0
24-02-2012	0	8400
29-02-2012	0	0
02-03-2012	0	4400
30-03-2012	0	5000
31-03-2012	0	0
30-04-2012	0	0
31-05-2012	0	0
30-06-2012	0	0
31-07-2012	0	0
31-08-2012	0	0
30-09-2012	0	0
08-10-2012	0	5000
31-10-2012	0	0
30-11-2012	0	0
03-12-2012	0	5000
31-12-2012	0	0
24-01-2013	19372	0
31-01-2013	0	0
28-02-2013	0	0
31-03-2013	0	0
30-04-2013	0	0
16-05-2013	0	15000
31-05-2013	0	0
06-06-2013	0	10000
30-06-2013	0	0
02-07-2013	0	16000
31-07-2013	0	0
12-08-2013	0	13000
31-08-2013	0	0
30-09-2013	0	0
31-10-2013	0	0
30-11-2013	0	0
09-12-2013	0	4000
31-12-2013	0	0
23-01-2014	18870	0
28-01-2014	0	7000
31-01-2014	0	0
28-02-2014	0	0
03-03-2014	0	8000

I want to put each month end date between dates. Can it possible withour VBA?

Thanks

 

[JamesCanale]

I think this will work in excel 2021. It's a bit less cumbersome in 365.

MrExcelPlayground19.xlsx
	A	B	C	D	E	F	G	H
1	Date	Debit	Credit			Date	Debit	Credit
2	2/12/2011	9101				2/12/2011	$9,101	$0
3	12/31/2011		65000			2/28/2011	$0	$0
4	1/18/2012		2000			3/31/2011	$0	$0
5	2/24/2012		8400			4/30/2011	$0	$0
6	3/2/2012		4400			5/31/2011	$0	$0
7	3/30/2012		5000			6/30/2011	$0	$0
8	10/8/2012		5000			7/31/2011	$0	$0
9	12/3/2012		5000			8/31/2011	$0	$0
10	1/24/2013	19372				9/30/2011	$0	$0
11	5/16/2013		15000			10/31/2011	$0	$0
12	6/6/2013		10000			11/30/2011	$0	$0
13	7/2/2013		16000			12/31/2011	$0	$65,000
14	8/12/2013		13000			1/18/2012	$0	$2,000
15	12/9/2013		4000			1/31/2012	$0	$0
16	1/23/2014	18870				2/24/2012	$0	$8,400
17	1/28/2014		7000			2/29/2012	$0	$0
18	3/3/2014		8000			3/2/2012	$0	$4,400
19						3/30/2012	$0	$5,000
20						3/31/2012	$0	$0
21						4/30/2012	$0	$0
22						5/31/2012	$0	$0
23						6/30/2012	$0	$0
24						7/31/2012	$0	$0
25						8/31/2012	$0	$0
26						9/30/2012	$0	$0
27						10/8/2012	$0	$5,000
28						10/31/2012	$0	$0
29						11/30/2012	$0	$0
30						12/3/2012	$0	$5,000
31						12/31/2012	$0	$0
32						1/24/2013	$19,372	$0
33						1/31/2013	$0	$0
34						2/28/2013	$0	$0
35						3/31/2013	$0	$0
36						4/30/2013	$0	$0
37						5/16/2013	$0	$15,000
38						5/31/2013	$0	$0
39						6/6/2013	$0	$10,000
40						6/30/2013	$0	$0
41						7/2/2013	$0	$16,000
42						7/31/2013	$0	$0
43						8/12/2013	$0	$13,000
44						8/31/2013	$0	$0
45						9/30/2013	$0	$0
46						10/31/2013	$0	$0
47						11/30/2013	$0	$0
48						12/9/2013	$0	$4,000
49						12/31/2013	$0	$0
50						1/23/2014	$18,870	$0
51						1/28/2014	$0	$7,000
52						1/31/2014	$0	$0
53						2/28/2014	$0	$0
54						3/3/2014	$0	$8,000
Sheet16

Cell Formulas
Range	Formula
F2:F54	F2	=LET(z,A2:A18,a,MIN(z),b,MAX(z),aa,YEAR(a)*12+MONTH(a),bb,YEAR(b)*12+MONTH(b),c,bb-aa,d,EOMONTH(a,SEQUENCE(c,1,0)),g,ROWS(z),h,ROWS(d),j,SEQUENCE(g+h),e,IF(j<=g,INDEX(z,j),INDEX(d,j-g)),f,SORT(UNIQUE(e)),f)
G2:G54	G2	=XLOOKUP(F2#,A2:A18,B2:B18,0,0)
H2:H54	H2	=XLOOKUP(F2#,A2:A18,C2:C18,0,0)
Dynamic array formulas.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1	Date	Debit	Credit
2	12/02/2011	9101				12/02/2011	9101	0
3	31/12/2011		65000			28/02/2011	0	0
4	18/01/2012		2000			31/03/2011	0	0
5	24/02/2012		8400			30/04/2011	0	0
6	02/03/2012		4400			31/05/2011	0	0
7	30/03/2012		5000			30/06/2011	0	0
8	08/10/2012		5000			31/07/2011	0	0
9	03/12/2012		5000			31/08/2011	0	0
10	24/01/2013	19372				30/09/2011	0	0
11	16/05/2013		15000			31/10/2011	0	0
12	06/06/2013		10000			30/11/2011	0	0
13	02/07/2013		16000			31/12/2011	0	65000
14	12/08/2013		13000			18/01/2012	0	2000
15	09/12/2013		4000			31/01/2012	0	0
16	23/01/2014	18870				24/02/2012	0	8400
17	28/01/2014		7000			29/02/2012	0	0
18	03/03/2014		8000			02/03/2012	0	4400
19						30/03/2012	0	5000
20						31/03/2012	0	0
21						30/04/2012	0	0
22						31/05/2012	0	0
23						30/06/2012	0	0
24						31/07/2012	0	0
25						31/08/2012	0	0
26						30/09/2012	0	0
27						08/10/2012	0	5000
28						31/10/2012	0	0
29						30/11/2012	0	0
30						03/12/2012	0	5000
31						31/12/2012	0	0
32						24/01/2013	19372	0
33						31/01/2013	0	0
34						28/02/2013	0	0
35						31/03/2013	0	0
36						30/04/2013	0	0
37						16/05/2013	0	15000
38						31/05/2013	0	0
39						06/06/2013	0	10000
40						30/06/2013	0	0
41						02/07/2013	0	16000
42						31/07/2013	0	0
43						12/08/2013	0	13000
44						31/08/2013	0	0
45						30/09/2013	0	0
46						31/10/2013	0	0
47						30/11/2013	0	0
48						09/12/2013	0	4000
49						31/12/2013	0	0
50						23/01/2014	18870	0
51						28/01/2014	0	7000
52						31/01/2014	0	0
53						28/02/2014	0	0
54						03/03/2014	0	8000
Master

Cell Formulas
Range	Formula
F2:F54	F2	=LET(a,A2:A18,r,ROWS(a),l,CEILING(YEARFRAC(A2,MAX(a))*12,1),em,EOMONTH(A2,SEQUENCE(l+r,,0)),s,SEQUENCE(l+r),SORT(UNIQUE(IF(s<=r,a,INDEX(em,s-r)))))
G2:H54	G2	=SUMIFS(B:B,$A:$A,$F2#)
Dynamic array formulas.

 

[lrobbo314]

I went for a 1 cell solution. Probably overly complex compared to the others.

Record on Open.xlsm
	A	B	C	D	E	F	G
1	Date	Debit	Credit		Date	Debit	Credit
2	12-02-2011	9101			12-02-2011	9101	0
3	31-12-2011		65000		28-02-2011	0	0
4	18-01-2012		2000		31-03-2011	0	0
5	24-02-2012		8400		30-04-2011	0	0
6	02-03-2012		4400		31-05-2011	0	0
7	30-03-2012		5000		30-06-2011	0	0
8	08-10-2012		5000		31-07-2011	0	0
9	03-12-2012		5000		31-08-2011	0	0
10	24-01-2013	19372			30-09-2011	0	0
11	16-05-2013		15000		31-10-2011	0	0
12	06-06-2013		10000		30-11-2011	0	0
13	02-07-2013		16000		31-12-2011	0	65000
14	12-08-2013		13000		18-01-2012	0	2000
15	09-12-2013		4000		31-01-2012	0	0
16	23-01-2014	18870			24-02-2012	0	8400
17	28-01-2014		7000		29-02-2012	0	0
18	03-03-2014		8000		02-03-2012	0	4400
19					30-03-2012	0	5000
20					31-03-2012	0	0
21					30-04-2012	0	0
22					31-05-2012	0	0
23					30-06-2012	0	0
24					31-07-2012	0	0
25					31-08-2012	0	0
26					30-09-2012	0	0
27					08-10-2012	0	5000
28					31-10-2012	0	0
29					30-11-2012	0	0
30					03-12-2012	0	5000
31					31-12-2012	0	0
32					24-01-2013	19372	0
33					31-01-2013	0	0
34					28-02-2013	0	0
35					31-03-2013	0	0
36					30-04-2013	0	0
37					16-05-2013	0	15000
38					31-05-2013	0	0
39					06-06-2013	0	10000
40					30-06-2013	0	0
41					02-07-2013	0	16000
42					31-07-2013	0	0
43					12-08-2013	0	13000
44					31-08-2013	0	0
45					30-09-2013	0	0
46					31-10-2013	0	0
47					30-11-2013	0	0
48					09-12-2013	0	4000
49					31-12-2013	0	0
50					23-01-2014	18870	0
51					28-01-2014	0	7000
52					31-01-2014	0	0
53					28-02-2014	0	0
54					03-03-2014	0	8000
55					31-03-2014	0	0
Sheet2

Cell Formulas
Range	Formula
E1:G55	E1	=LET(h,A1:C1,dt,A2:A18,d,B2:B18,c,C2:C18,mnd,MIN(dt),mxd,MAX(dt),di,DATEDIF(mnd,mxd,"m")+2,eo,MAP(INDEX(INDEX(dt,1),SEQUENCE(di,,,0)),SEQUENCE(di,,0),LAMBDA(x,y,EOMONTH(x,y))),u,UNIQUE(SORT(VSTACK(dt,eo))),dbt,XLOOKUP(u,dt,d,0),cdt,XLOOKUP(u,dt,c,0),VSTACK(h,HSTACK(u,dbt,cdt)))
Dynamic array formulas.

 

[JamesCanale]

I don't think those "STACKS" will work in 2021.

 

[Fluff]

Nor will the MAP function.

 

[lrobbo314]

You know, I just checked LET and FILTER, and both came up as available in 2021. I just figured it had all of them by now. Oh well, good to know guys.

 

[Dave Patton]

I did not see the reason for the month end dates.
What calculations will you do with this information?

 

[Santosh080]

thanks for help. When try the above formulas i get this error. Help me with this error.

 

[Fluff]

Do you normally use a comma as the separator in a formula, or do you use semi-colons?