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

 

[Santosh080]

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

comma

 

[Fluff]

Did you copy paste the formula or type it in?

 

[Santosh080]

Did you copy paste the formula or type it in?

I just try it by copy paste.

 

[Fluff]

Try copying the entire sheet using the copy icon

 

[Santosh080]

not working...getting same error.

 

[Fluff]

Try doing it with the mini sheet I posted.

 

[Santosh080]

after pasting it look like this,

Date	Debit	Credit
40586	9101				=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)))))	=SUMIFS(B:B,$A:$A,$F2#)	=SUMIFS(C:C,$A:$A,$F2#)
40908		65000
40926		2000
40963		8400
40970		4400
40998		5000
41190		5000
41246		5000
41298	19372
41410		15000
41431		10000
41457		16000
41498		13000
41617		4000
41662	18870
41667		7000
41701		8000

 

[MARK858]

Format your date column as Date

 

[Fluff]

after pasting it look like this,

Unfirtunately I have no idea why your version of Excel will not accept those formulae.

 

[MARK858]

@Santosh080 Can you save the file in the link below to your computer, then open it and let us know what you see on sheet1

New Test.xlsx | Powered by Box

app.box.com