Ageing

[ryan7]

I could not find an ageing thread that answered this clearly.
I have the below dates and corresponding values listed under Amount.
I've marked the ageing bucket and what should be the correct value but I'd like to know the correct formula to save time for each.
Thanks

Date	Amount		Ageing	Totals
05/02/2021​	£ 49.96		Current	£ 1,633.91
06/04/2021​	£ 9.83		1-30 days	£ 128.98
18/05/2021​	£ 2.23		31-60 days	-£ 30.30
18/05/2021​	£ 16.06		61-90 days	0​
06/09/2021​	£ 137.15		91-120 days	£ 229.30
21/09/2021​	£ 92.15		121+ Days	£ 78.08
11/11/2021​	-£ 30.30		Total	£ 2,039.97
20/12/2021​	£ 66.96
24/12/2021​	£ 45.00
24/12/2021​	£ 1.90
31/12/2021​	£ 15.12
05/01/2022​	-£ 8.24
07/01/2022​	£ 188.69
10/01/2022​	£ 1,235.60
10/01/2022​	£ 31.21
10/01/2022​	£ 186.65

 

[etaf]

i'm not getting the same results , using SUM() for current
also I'm using a SUMIF() and a SUMIFS()
for the dates

is this 30days from today() or 30days from the latest date in list

so you can see how i have structured the formulas - so a SUMIFS() for the other ranges

Book6
	A	B	C	D	E	F	G
1	Date	Amount		Ageing	Totals	TODAY	MAX date
2	2/5/21	$49.96		Current	$1,633.91	$2,039.97	$2,039.97
3	4/6/21	$9.83		1-30 days	$128.98	$195.57	$309.43
4	5/18/21	$2.23		31-60 days	($30.30)	$113.86	$0.00
5	5/18/21	$16.06		61-90 days	0
6	9/6/21	$137.15		91-120 days	$229.30
7	9/21/21	$92.15		121+ Days	$78.08
8	11/11/21	($30.30)		Total	$2,039.97
9	12/20/21	$66.96
10	12/24/21	$45.00
11	12/24/21	$1.90
12	12/31/21	$15.12
13	1/5/22	($8.24)
14	1/7/22	$188.69
15	1/10/21	$1,235.60
16	1/10/21	$31.21
17	1/10/21	$186.65
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUM(B2:B17)
G2	G2	=SUM(B2:B17)
F3	F3	=SUMIF(A2:A17,">="&TODAY()-30,B2:B17)
G3	G3	=SUMIF(A2:A17,">="&MAX(A2:A17)-30,B2:B17)
F4:G4	F4	=SUMIFS(B2:B17,A2:A17,">="&TODAY()-60, A2:A17,"<"&TODAY()-30)

 

[Joe4]

Have a look here for directions and explanations of how to use SUMIFS for date ranges.

Excel SUMIFS date range formula - sum if between two dates

See how to use the SUMIFS in Excel with date range as criteria to sum values between two dates.

www.ablebits.com

 

[ryan7]

i'm not getting the same results , using SUM() for current
also I'm using a SUMIF() and a SUMIFS()
for the dates

is this 30days from today() or 30days from the latest date in list

so you can see how i have structured the formulas - so a SUMIFS() for the other ranges

Book6
	A	B	C	D	E	F	G
1	Date	Amount		Ageing	Totals	TODAY	MAX date
2	2/5/21	$49.96		Current	$1,633.91	$2,039.97	$2,039.97
3	4/6/21	$9.83		1-30 days	$128.98	$195.57	$309.43
4	5/18/21	$2.23		31-60 days	($30.30)	$113.86	$0.00
5	5/18/21	$16.06		61-90 days	0
6	9/6/21	$137.15		91-120 days	$229.30
7	9/21/21	$92.15		121+ Days	$78.08
8	11/11/21	($30.30)		Total	$2,039.97
9	12/20/21	$66.96
10	12/24/21	$45.00
11	12/24/21	$1.90
12	12/31/21	$15.12
13	1/5/22	($8.24)
14	1/7/22	$188.69
15	1/10/21	$1,235.60
16	1/10/21	$31.21
17	1/10/21	$186.65
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUM(B2:B17)
G2	G2	=SUM(B2:B17)
F3	F3	=SUMIF(A2:A17,">="&TODAY()-30,B2:B17)
G3	G3	=SUMIF(A2:A17,">="&MAX(A2:A17)-30,B2:B17)
F4:G4	F4	=SUMIFS(B2:B17,A2:A17,">="&TODAY()-60, A2:A17,"<"&TODAY()-30)

Current will refer to January dates only.
1-30 Days, December only
31-60 Days, November
etc..

 

[etaf]

so if you open the spreadsheet on the 1st Feb, then
Current = Feb,
1-30 = Jan
31-60 = Dec previous year

they are only 30 days - but months have 28,30,31 days in

 

[jtakw]

Hi,

This works Based on Months, Not days:

Book3.xlsx
	A	B	C	D	E
1	Date	Amount		Ageing	Totals
2	2/5/2021	$49.96		Current	1,633.91
3	4/6/2021	$9.83		1 Month	128.98
4	5/18/2021	$2.23		2 Months	-30.30
5	5/18/2021	$16.06		3 Months	0.00
6	9/6/2021	$137.15		4 Months	229.30
7	9/21/2021	$92.15		4 Months+	78.08
8	11/11/2021	-$30.30		Total	2,039.97
9	12/20/2021	$66.96
10	12/24/2021	$45.00
11	12/24/2021	$1.90
12	12/31/2021	$15.12
13	1/5/2022	-$8.24
14	1/7/2022	$188.69
15	1/10/2021	$1,235.60
16	1/10/2021	$31.21
17	1/10/2021	$186.65
Sheet956

Cell Formulas
Range	Formula
E2:E6	E2	=SUMPRODUCT((MONTH(A$2:A$17)=MONTH(EDATE(TODAY(),-ROWS(E$2:E2)+1)))*B$2:B$17)
E7	E7	=E8-SUM(E2:E6)
E8	E8	=SUM(B2:B17)

 

[ryan7]

Hi,

This works Based on Months, Not days:

Book3.xlsx
	A	B	C	D	E
1	Date	Amount		Ageing	Totals
2	2/5/2021	$49.96		Current	1,633.91
3	4/6/2021	$9.83		1 Month	128.98
4	5/18/2021	$2.23		2 Months	-30.30
5	5/18/2021	$16.06		3 Months	0.00
6	9/6/2021	$137.15		4 Months	229.30
7	9/21/2021	$92.15		4 Months+	78.08
8	11/11/2021	-$30.30		Total	2,039.97
9	12/20/2021	$66.96
10	12/24/2021	$45.00
11	12/24/2021	$1.90
12	12/31/2021	$15.12
13	1/5/2022	-$8.24
14	1/7/2022	$188.69
15	1/10/2021	$1,235.60
16	1/10/2021	$31.21
17	1/10/2021	$186.65
Sheet956

Cell Formulas
Range	Formula
E2:E6	E2	=SUMPRODUCT((MONTH(A$2:A$17)=MONTH(EDATE(TODAY(),-ROWS(E$2:E2)+1)))*B$2:B$17)
E7	E7	=E8-SUM(E2:E6)
E8	E8	=SUM(B2:B17)

I want to build this into a macro. How do I correctly capture the correct number of rows when they will be different each time. In it's current form it returns 0's when a different number of rows .

 

[Joe4]

You can find the last row number of any column like this (example for column A)

Dim lastRow as Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

 

[ryan7]

You can find the last row number of any column like this (example for column A)

Dim lastRow as Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

So, in the below example, how would the highlighted parts read in a formula that finds the last row each time? Thank You

=SUMPRODUCT((MONTH(A$2:A$17)=MONTH(EDATE(TODAY(),-ROWS(E$2:E2)+1)))*B$2:B$17)

 

[Joe4]

I though you said that you wanted to do it with a Macro?

In VBA, to apply that formula somewhere, you could do it like this:

Range(...).Formula = "=SUMPRODUCT((MONTH(A$2:A$" &  lastRow & ")=MONTH(EDATE(TODAY(),-ROWS(E$2:E2)+1)))*B$2:B$" & lastRow  & ")"