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]

@Fluff
Today i have re-installed my office and it woks.I think there is issue with Office. Thanks.

But i have an issue with formula, when there is two same date it merge like this. But i do not want to merge the Credit or Debit Amt.

In data ,

03-08-2011​	2482.00​
03-08-2011​	6000.00​

I want result ,

03-08-2011​	2482.00​
03-08-2011​	6000.00​
31-08-2011​	0.00​

Date	Debit	Credit
23-04-2011	1000.00						00-01-1900	0	0
12-05-2011	10000.00						23-04-2011	1000	0
14-05-2011	200000.00						30-04-2011	0	0
26-05-2011	10000.00						12-05-2011	10000	0
02-06-2011	25000.00						14-05-2011	200000	0
10-06-2011	21000.00						26-05-2011	10000	0
13-06-2011	50000.00						31-05-2011	0	0
18-06-2011	102000.00						02-06-2011	25000	0
23-06-2011	2000.00						10-06-2011	21000	0
07-07-2011	60000.00						13-06-2011	50000	0
13-07-2011	25000.00						18-06-2011	102000	0
03-08-2011	2482.00						23-06-2011	2000	0
03-08-2011	6000.00						30-06-2011	0	0
10-08-2011	30000.00						07-07-2011	60000	0
12-08-2011	15000.00						13-07-2011	25000	0
20-08-2011	15000.00						31-07-2011	0	0
25-08-2011	25000.00						03-08-2011	8482	0
22-09-2011	25000.00						10-08-2011	30000	0
24-09-2011	25000.00						12-08-2011	15000	0
28-09-2011	25000.00						20-08-2011	15000	0
29-09-2011	0.00	25000.00					25-08-2011	25000	0
29-09-2011	0.00	29000.00					31-08-2011	0	0
08-10-2011	25000.00						22-09-2011	25000	0
12-10-2011	25000.00						24-09-2011	25000	0
13-10-2011	50000.00						28-09-2011	25000	0
15-10-2011	25000.00						29-09-2011	0	54000
20-10-2011	5000.00						30-09-2011	0	0
25-10-2011	5000.00						08-10-2011	25000	0

 

[Fluff]

Ok, how about

=LET(a,A2:A29,r,ROWS(a),l,CEILING(YEARFRAC(A2,MAX(a))*12,1),em,EOMONTH(A2,SEQUENCE(l+r,,0)),s,SEQUENCE(l+r),SORT(CHOOSE({1,2,3},IF(s<=r,a,INDEX(em,s-r)),IF(s<=r,B2:B29,0),IF(s<=r,C2:C29,0))))

 

[Santosh080]

thanks Fluff
this works great. I have face an issue . When i increase the range (A Cell) but my data are less it generate some random dates like this.

formula i set,

Result i get

One request . At home i am using office 2021 and this works but at work place they have office 2016 and 2019. This formula not working in old office. Do you have any solution for this. Thanks

 

[Fluff]

Those are not random dates, they are 0 formatted as a date because you have included blank cells.

at work place they have office 2016 and 2019. This formula not working in old office.

Why didn't you say that at the start?

 

[Fluff]

This will allow you to use a large range

=LET(a,FILTER(A2:C100,A2:A100<>""),r,ROWS(a),l,CEILING(YEARFRAC(A2,MAX(INDEX(a,,1)))*12,1),em,EOMONTH(A2,SEQUENCE(l+r,,0)),s,SEQUENCE(l+r),SORT(CHOOSE({1,2,3},IF(s<=r,INDEX(a,,1),INDEX(em,s-r)),IF(s<=r,INDEX(a,,2),0),IF(s<=r,INDEX(a,,3),0))))

 

[Santosh080]

Those are not random dates, they are 0 formatted as a date because you have included blank cells.

Why didn't you say that at the start?

sorry for that. I believed I could handle the data work from home initially, but due to a recent increase in workload, I have been unable to manage it. I find myself spending more hours at the office now. Therefore, if you have a solution for this, it would be greatly appreciated. Thank you.

 

[Fluff]

I cannot think of a way of doing it for 2019.

 

[Dave Patton]

You did not answer my questions in an early post!
If you want to try a very simple approach, consider the following.

Add a range of month end dates below your data.
You can leave the adjacent columns blank or include 0.

Sort the range on the date field.
You may have some duplicate month end dates. If these are a problem, delete them.