Star (Min) and End (Max) date claculation from series of dates in table

[ateeqsindhu]

Facing trouble calculating Star(Min) and End(Max) dates from a table so a to show start and end date of a closing balance amount appeared in respective GL. this is a minute extract of huge data set. also need to calculate duration (days) from start to end and ageing of same.

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Sr. No.	Branch Code	Date	GL Code	Closing Balance	Start	End	Days (no. of days from start till end)	Count Trns.(count of unique Closing Balances)	Ageing (w.r.t. how many days closing balance remained in this table, slabs are 0-30days, 31-60days, 61-90days, 91-180days & 18+)
2	8400	38	11-Nov-19	105130028223	-5
3	8437	38	12-Nov-19	105130028223	-5
4	8468	38	13-Nov-19	105130028223	-5
5	8532	38	15-Nov-19	105130028223	-5
6	8558	38	16-Nov-19	105130028223	-5
7	8584	38	17-Nov-19	105130028223	-5
8	8609	38	18-Nov-19	105130028223	-5
9	2053	38	18-Apr-19	105130028223	4
10	2100	38	20-Apr-19	105130028223	4
11	2146	38	21-Apr-19	105130028223	4
12	2196	38	22-Apr-19	105130028223	4
13	2364	38	26-Apr-19	105130028223	4
14	2404	38	27-Apr-19	105130028223	4
15	2444	38	28-Apr-19	105130028223	4
16	16880	38	17-Oct-20	105130028223	4
17	16891	38	18-Oct-20	105130028223	4
Sheet1

 

[alz]

what is your expected answer on column J on your given Question?

how negative value will be count?

 

[ateeqsindhu]

what is your expected answer on column J on your given Question?

how negative value will be count?

It may be either (best option) separate column for 0-30days, 31-60days etc……. and a Tik mark appears where ever that figure falls OR in single column highlighting either as 0-30days, 31-60days etc.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Star (Min) and End (Max) date claculation from series of dates in table
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[alz]

Check this out!.

Excel Message board.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Sr. No.	Branch Code	Date	GL Code	Closing Balance		GL Code	Start	End	Days (no. of days from start till end)	Count Trns.(count of unique Closing Balances)	Ageing (w.r.t. how many days closing balance remained in this table, slabs are 0-30days, 31-60days, 61-90days, 91-180days & 180+)
2	8400	38	11/11/2019	105130028223	-5		105130028223	18/04/2019	18/10/2020	549	2	180 +
3	8437	38	12/11/2019	105130028223	-5
4	8468	38	13/11/2019	105130028223	-5
5	8532	38	15/11/2019	105130028223	-5
6	8558	38	16/11/2019	105130028223	-5
7	8584	38	17/11/2019	105130028223	-5
8	8609	38	18/11/2019	105130028223	-5
9	2053	38	18/04/2019	105130028223	4
10	2100	38	20/04/2019	105130028223	4
11	2146	38	21/04/2019	105130028223	4
12	2196	38	22/04/2019	105130028223	4
13	2364	38	26/04/2019	105130028223	4
14	2404	38	27/04/2019	105130028223	4
15	2444	38	28/04/2019	105130028223	4
16	16880	38	17/10/2020	105130028223	4
17	16891	38	18/10/2020	105130028223	4
49

Cell Formulas
Range	Formula
H2	H2	=AGGREGATE(15,6,$C$2:$C$17/($D$2:$D$17=$G2),1)
I2	I2	=AGGREGATE(14,6,$C$2:$C$17/($D$2:$D$17=$G2),1)
J2	J2	=I2-H2
K2	K2	=SUM(--((FREQUENCY(IF($D$2:$D$17=G2,$E$2:$E$17),$E$2:$E$17))>0))
L2	L2	=IF(J2<=30," 0-31 Days",IF(J2<=60,"31-60 Days",IF(J2<=90,"61-90 Days",IF(J2<=180,"91-180 Days","180 + "))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ateeqsindhu]

Check this out!.

Excel Message board.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Sr. No.	Branch Code	Date	GL Code	Closing Balance		GL Code	Start	End	Days (no. of days from start till end)	Count Trns.(count of unique Closing Balances)	Ageing (w.r.t. how many days closing balance remained in this table, slabs are 0-30days, 31-60days, 61-90days, 91-180days & 180+)
2	8400	38	11/11/2019	105130028223	-5		105130028223	18/04/2019	18/10/2020	549	2	180 +
3	8437	38	12/11/2019	105130028223	-5
4	8468	38	13/11/2019	105130028223	-5
5	8532	38	15/11/2019	105130028223	-5
6	8558	38	16/11/2019	105130028223	-5
7	8584	38	17/11/2019	105130028223	-5
8	8609	38	18/11/2019	105130028223	-5
9	2053	38	18/04/2019	105130028223	4
10	2100	38	20/04/2019	105130028223	4
11	2146	38	21/04/2019	105130028223	4
12	2196	38	22/04/2019	105130028223	4
13	2364	38	26/04/2019	105130028223	4
14	2404	38	27/04/2019	105130028223	4
15	2444	38	28/04/2019	105130028223	4
16	16880	38	17/10/2020	105130028223	4
17	16891	38	18/10/2020	105130028223	4
49

Cell Formulas
Range	Formula
H2	H2	=AGGREGATE(15,6,$C$2:$C$17/($D$2:$D$17=$G2),1)
I2	I2	=AGGREGATE(14,6,$C$2:$C$17/($D$2:$D$17=$G2),1)
J2	J2	=I2-H2
K2	K2	=SUM(--((FREQUENCY(IF($D$2:$D$17=G2,$E$2:$E$17),$E$2:$E$17))>0))
L2	L2	=IF(J2<=30," 0-31 Days",IF(J2<=60,"31-60 Days",IF(J2<=90,"61-90 Days",IF(J2<=180,"91-180 Days","180 + "))))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks dear

 

[ateeqsindhu]

Thanks dear

dear would appreciate if you could also help me on another issue in another table extracted from above table

Month	Start	End	Days	Days in Current Month
02	02/01/2020	02/25/2020	25	25
02	01/15/2020	02/08/2020	25	8
02	01/26/2020	03/05/2020	40	29
02	02/22/2020	03/21/2020	29	8

i want to calculate # in "Days in Current Month" column now if i use separate formulas (04 scenarios) then calculation is ok but when i use all these formulas in combination it returns # as column "Days" SEPERATELY;
(1) =IF(AND(MONTH(N11446=M11446),(MONTH(O11446=M11446))),(O11446-N11446)+1,"")
(2) =IF(AND(MONTH(N11447<M11447),MONTH(O11447=M11447)),(O11447-(EOMONTH(M11447,-1)+1)+1),"")
(3) =IF(AND(MONTH(N11448<M11448),MONTH(O11448>M11448)),((EOMONTH(M11448,0))-(EOMONTH(M11448,-1)+1)+1),"")
(4) =IF(AND(MONTH(N11449=M11449),MONTH(O11449>M11449)),((EOMONTH(M11449,0)-N11449)+1),"")
Combined:
IF(AND(MONTH(N11446=M11446),(MONTH(O11446=M11446))),(O11446-N11446)+1,IF(AND(MONTH(N11447<M11447),MONTH(O11447=M11447)),(O11447-(EOMONTH(M11447,-1)+1)+1),IF(AND(MONTH(N11448<M11448),MONTH(O11448>M11448)),((EOMONTH(M11448,0))-(EOMONTH(M11448,-1)+1)+1),IF(AND(MONTH(N11449=M11449),MONTH(O11449>M11449)),((EOMONTH(M11449,0)-N11449)+1),""))))