Today’s challenge is contributed by 🇰🇷 Taeyong Shin Divide the payments among different quarters / months. Have a total amount line at the end. This challenge totals the amount from the 26th of the previous month to the 25th of the current month.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 676
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Quarterly Payment Grouping with Power Query
Power Query solution 1 for Quarterly Payment Grouping, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Ans",
each [
a = [Date],
b = if Date.Day(a) < 26 or Date.Month(a) = 12 then a else Date.AddMonths(a, 1),
Month = Text.Start(Date.MonthName(b), 3),
Quarter = "Q" & Text.From(Date.QuarterOfYear(b))
]
),
C = Table.ExpandRecordColumn(B, "Ans", {"Quarter", "Month"}),
D = Table.Group(C, {"Quarter", "Month"}, {"Payment", each List.Sum([Payment])}),
E = D & Table.Group(D, {}, {{"Quarter", each "Total"}, {"Payment", each List.Sum([Payment])}})
in
EPower Query solution 2 for Quarterly Payment Grouping, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = each Date.AddDays(_, Date.DaysInMonth(_) - 25),
Group = Table.Group(
Source,
"Date",
{
{"Quarter", each "Q" & Text.From(Date.QuarterOfYear(Fx(List.Min([Date]))))},
{"Month", each DateTime.ToText(Fx(List.Min([Date])), "MMM")},
{"Payment", each List.Sum([Payment])}
},
0,
(x, y) => Number.From(Date.Month(Fx(y)) <> Date.Month(Fx(x)))
)[[Quarter], [Month], [Payment]],
Final = Group
& Table.FromRows({{"Total", null, List.Sum(Group[Payment])}}, Table.ColumnNames(Group))
in
FinalPower Query solution 3 for Quarterly Payment Grouping, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = Table.TransformColumns(
Source,
{{"Date", each if Date.Day(_) > 25 then Date.EndOfMonth(_) + Duration.From(1) else _}}
),
Group = Table.Group(
Transform,
"Date",
{
{
"New",
each {
"Q" & Text.From(Date.QuarterOfYear([Date]{0})),
Text.Start(Date.MonthName([Date]{0}), 3),
List.Sum([Payment])
}
}
},
0,
(x, y) => Value.Compare(Date.MonthName(x), Date.MonthName(y))
),
SelectYear = Table.SelectRows(Group, each Date.Year(List.Min(Group[Date])) = Date.Year([Date]))[
New
],
Result = Table.FromRows(
SelectYear & {{"Total", "", List.Sum(List.Transform(SelectYear, each _{2}))}},
{"Qtr", "Month", "Payment"}
)
in
ResultPower Query solution 4 for Quarterly Payment Grouping, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Custom1 = Table.TransformColumns(
Source,
{
"Date",
(x) =>
if Date.Day(x) >= 26 and Date.Month(x) < 12 then Date.AddDays(Date.EndOfMonth(x), 1) else x
}
),
grup = Table.RemoveColumns(
Table.Group(
Custom1,
{"Date"},
{
{"Quarter", each "Q" & Text.From(Date.QuarterOfYear(List.Max([Date])))},
{"Month", each Date.ToText(List.Max([Date]), [Format = "MMM"])},
{"Payment", each List.Sum([Payment])}
},
GroupKind.Local,
(x, y) =>
let
q = Value.Compare(Date.QuarterOfYear(x[Date]), Date.QuarterOfYear(y[Date])),
m = Value.Compare(Date.Month(x[Date]), Date.Month(y[Date])),
z = if q = 0 then m else q
in
z
),
"Date"
),
Custom2 = [
v = List.Sum(grup[Payment]),
tr = {"Total", "", v},
tbl = Table.FromRows(Table.ToRows(grup) & {tr}, Table.ColumnNames(grup))
][tbl]
in
Custom2Solving the challenge of Quarterly Payment Grouping with Excel
Excel solution 1 for Quarterly Payment Grouping, proposed by Rick Rothstein:
=LET(r,
A2:A101,
m,
SEQUENCE(
12
),
y,
YEAR(
@r
),
HSTACK(TEXT(
{10,
28}*m,
{"Qm",
"mmm"}
),
SUMIFS(OFFSET(
r,
,
1
),
r,
">"&DATE(y-(m=1),
m-1,
25),
r,
"<"&DATE(
y,
m,
26
))))
With header and footer...
=LET(r,
A2:A101,
m,
SEQUENCE(
12
),
y,
YEAR(
@r
),
h,
HSTACK(TEXT(
{10,
28}*m,
{"Qm",
"mmm"}
),
SUMIFS(OFFSET(
r,
,
1
),
r,
">"&DATE(y-(m=1),
m-1,
25),
r,
"<"&DATE(
y,
m,
26
))),
VSTACK(
{"Quarter",
"Month",
"Payment"},
h,
HSTACK(
"Total",
"",
SUM(
TAKE(
h,
,
-1
)
)
)
))Excel solution 2 for Quarterly Payment Grouping, proposed by John V.:
=CHOOSECOLS(GROUPBY(TEXT({10,
30,
30}*MONTH(EDATE(A2:A101-(MONTH(
A2:A101
)=12)-25,
1)),
{"Qm",
"mm",
"mmm"}),
B2:B101,
SUM),
1,
3,
4)Excel solution 3 for Quarterly Payment Grouping, proposed by 🇰🇷 Taeyong Shin:
=LET(d,A2:A101,n,(d>(EOMONTH(+d,-1)+25))+MONTH(d),m,n-(n=13),CHOOSECOLS(GROUPBY(TEXT(HSTACK(MONTH(m*10),m,m&-1),{"Q0","0","mmm"}),B2:B101,SUM),1,3,4))Excel solution 4 for Quarterly Payment Grouping, proposed by Kris Jaganah:
=LET(a,A2:A101,b,MONTH(a),c,IF((DAY(a)>25)*(b+1<=12),b+1,b),d,GROUPBY(HSTACK("Q"&ROUNDUP(c/3,0),c,TEXT(c*28,"MMM")),B2:B101,SUM),VSTACK({"Quarter","Month","Payment"},CHOOSECOLS(d,1,3,4)))Excel solution 5 for Quarterly Payment Grouping, proposed by Timothée BLIOT:
=LET(A,A2:A101,B,IF(DAY(A)>25,DATE(2025,IF(MONTH(A)=12,12,MONTH(A)+1),1),A),C,GROUPBY(HSTACK("Q"&CEILING(MONTH(B)/3,1),TEXT(B,"mmm")),B2:B101,SUM),SORTBY(C,XMATCH(INDEX(C,,2),TEXT(DATE(2025,ROW(1:12),1),"mmm"))))Excel solution 6 for Quarterly Payment Grouping, proposed by Oscar Mendez Roca Farell:
=LET(d,A2:A101,n,IFERROR(1/(1/MOD(MONTH(d)-(DAY(d)<26)+1,13)),12), g, GROUPBY(HSTACK("Q"&INT((n-1)/3)+1,n&-25),B2:B101,SUM,,1), IFERROR(--g,g))Excel solution 7 for Quarterly Payment Grouping, proposed by Duy Tùng:
=GROUPBY(
TEXT(
MONTH(
EOMONTH(
A2:A101-25,
1
)
)*{10,
29},
{"Qm",
"mmm"}
),
B2:B101,
SUM
)Excel solution 8 for Quarterly Payment Grouping, proposed by Sunny Baggu:
=LET(
_d,
DATE(
2025,
SEQUENCE(
12
),
{25,
26}
),
_a,
VSTACK(
DATE(
2025,
1,
1
),
DROP(
TAKE(
_d,
,
-1
),
-1
)
),
_b,
VSTACK(
DROP(
TAKE(
_d,
,
1
),
-1
),
DATE(
2025,
12,
31
)
),
_v,
MAP(
_a,
_b,
LAMBDA(x,
y,
SUM((A2:A101 >= x) * (A2:A101 <= y) * B2:B101))
),
_r,
HSTACK(
"Q" & ROUNDUP(
SEQUENCE(
12
) / 3,
0
),
TEXT(
_a,
"mmm"
),
_v
),
VSTACK(
_r,
HSTACK(
{"Total",
""},
SUM(
_v
)
)
)
)Excel solution 9 for Quarterly Payment Grouping, proposed by Anshu Bantra:
= to_df(
REF(
"A1:B101"
)
)
df['Date'] = pd.to_datetime(
df['Date']
)
dates = df['Date']
df['this'] = df['Date'].apply(
lambda x: x.replace(
day=25
)
)
date_condition = (df['Date'].dt.day > 25)
df['Month'] = np.where(
date_condition,
df['Date'].dt.month+1,
(df['Date'].dt.month)
)
df['Month'] = np.where(df['Month']>12,
(df['Month']-1)%12+1,
df['Month'])
df['Year'] = np.where(
date_condition & (df['Date'].dt.month == 12),
df['Date'].dt.year+1,
df['Date'].dt.year
)
df['Qtr'] = ( ((df['Month']-1)%12) // 3) + 1
df.groupby(
by=['Year',
'Qtr',
'Month']
)
['Payment'].sum()
.reset_index()
.sort_values(
by=['Year',
'Qtr',
'Month']
)Excel solution 10 for Quarterly Payment Grouping, proposed by Anshu Bantra:
=LET(
data_,
A2:B101,
paym_,
CHOOSECOLS(
data_,
2
),
dates_,
CHOOSECOLS(
data_,
1
),
this_,
EOMONTH(
--M2#,
-1
) + 1 + 24,
mons_,
IF((dates_ <= this_),
MONTH(
this_
),
MONTH(
this_
) + 1),
years_,
IF((dates_ > this_) * (MONTH(
dates_
) = 12),
YEAR(
this_
) + 1,
YEAR(
this_
)),
months_,
IF(
mons_ > 12,
MOD(
mons_,
13
) + 1,
MOD(
mons_,
13
)
),
qtr_,
CHOOSE(
MATCH(
months_,
{1,
4,
7,
10}
),
"Q1",
"Q2",
"Q3",
"Q4"
),
mon_,
CHOOSE(
months_,
"Jan",
"Feb",
"Mar",
"Apr",
"May",
"Jun",
"Jul",
"Aug",
"Sep",
"Oct",
"Nov",
"Dec"
),
VSTACK(
{"Quarter",
"Month",
"Payment"},
DROP(
GROUPBY(
HSTACK(
years_,
mons_,
qtr_,
mon_
),
paym_,
SUM,
,
1
),
,
2
)
)
)Excel solution 11 for Quarterly Payment Grouping, proposed by Pieter de B.:
=LET(
a,
A2:A101,
x,
IF(
MONTH(
a
)=12,
12,
MONTH(
EOMONTH(
+a,
N(
DAY(
a
)>25
)
)
)
),
g,
DROP(
GROUPBY(
TEXT(
ROUNDUP(
x/{1,
3,
1},
)*{1,
1,
29},
{"00",
"Q0",
"mmm"}
),
B2:B101,
SUM
),
,
1
),
IF(
{1,
2,
3}=1,
IF(
g="",
"Total",
g
),
g
)
)Excel solution 12 for Quarterly Payment Grouping, proposed by Hamidi Hamid:
=LET(
x,
A2:A101,
s,
SCAN(
0,
x,
LAMBDA(
a,
b,
LET(
y,
IF(
DAY(
b
)>25,
MONTH(
b
)+1,
MONTH(
b*1
)
),
IF(
y>12,
12,
y
)
)
)
),
g,
GROUPBY(
s,
B2:B101,
SUM,
,
0
),
h,
HSTACK(
"Q"&ROUNDUP(
TAKE(
g,
,
1
)/3,
0,
5
),
PROPER(
TEXT(
"01/"&TAKE(
g,
,
1
),
"mmm"
)
),
TAKE(
g,
,
-1
)
),
VSTACK(
h,
HSTACK(
"Total",
"",
SUM(
TAKE(
g,
,
-1
)
)
)
)
)Excel solution 13 for Quarterly Payment Grouping, proposed by Eric Laforce:
=LET(d, BYROW(tData[Date],LAMBDA(d,IF(DAY(d)<=25, EOMONTH(d,0),EOMONTH(d,1)))),
g, GROUPBY(HSTACK(YEAR(d), "Q"&ROUNDUP(MONTH(d)/3,0), MONTH(d),TEXT(d,"mmm")), tData[Payment], SUM,0,0),
VSTACK(HSTACK("Quarter","Month","Payment"), CHOOSECOLS(g,2,4,5), HSTACK("Total","",SUM(CHOOSECOLS(g,5)))))Excel solution 14 for Quarterly Payment Grouping, proposed by Ankur Sharma:
=LET(c, CHOOSECOLS, Rng, A2:A101,
NewDt, IF(DAY(Rng) <= 25, Rng, DATE(YEAR(Rng), MONTH(Rng) + 1, 1)),
Qtr, "Q" & ROUNDUP(MONTH(NewDt)/3, 0),
A_1, GROUPBY(HSTACK(Qtr, MONTH(NewDt), YEAR(NewDt)), B2:B101, SUM),
A_2, SORT(A_1, 3),
Mnth, IFERROR(TEXT(--(c(A_2, 2) & "-" & c(A_2, 3)), "mmm"), ""),
HSTACK(c(A_2, 1), Mnth, c(A_2, 4)))Excel solution 15 for Quarterly Payment Grouping, proposed by Meganathan Elumalai:
=LET(a,A2:A101,ed,EDATE(+a,--(DAY(a)>25)),g,DROP(GROUPBY(HSTACK(TEXT(ed,{"yy","mm"}),"Q"&MONTH(MONTH(ed)*10),TEXT(ed,"mmm")),B2:B101,SUM,0,1,,YEAR(ed)=MIN(YEAR(ed))),-1,2),VSTACK(g,HSTACK("Total","",SUM(TAKE(g,,-1)))))Excel solution 16 for Quarterly Payment Grouping, proposed by Imam Hambali:
=LET(
d,A2:A101,
p,B2:B101,
dd,IF(DAY(d)>25,EDATE(--d,1),d),
f,FILTER(HSTACK(dd,p),YEAR(dd)=2025),
g,GROUPBY(HSTACK(TEXT(TAKE(f,,1),"m")*1,TEXT(TAKE(f,,1),"mmm")),TAKE(f,,-1),SUM,0,0),
t,HSTACK({"Total",""},SUM(TAKE(g,,-1))),
VSTACK({"Quarter","Month","Payment"},HSTACK(ROUNDUP(TAKE(g,,1)/3,0),DROP(g,,1)),t))Excel solution 17 for Quarterly Payment Grouping, proposed by Philippe Brillault:
=LET(C,CHOOSECOLS,x,C(_T;1),fm,MAP(DAY(x)>25,MOD(MONTH(x),2),XOR),ma,SCAN(0,MAP(fm,VSTACK(0,DROP(fm,-1)),XOR),SUM),q,"Q"&ROUNDUP(ma/3,0),g,GROUPBY(HSTACK(q,ma,TEXT(ma*28,"mmm")),C(_T;2),SUM,0,1,,&q<>"Q5"),C(g,1,3,4))
The december total = 52700 - 4900 = 47800
(Thanks for the trick TEXT(UserDefinedMonth*28,"mmm") 👍 )Excel solution 18 for Quarterly Payment Grouping, proposed by Philippe Brillault:
Kris Jaganah Excel solution 19 for Quarterly Payment Grouping, proposed by Erdit Qendro:
=LET(data,
A2:B101,
dt,
TAKE(
data,
,
1
),
m,
MONTH,
mo,
IF((DAY(
dt
)<26)+(m(
dt
)=12),
m(
dt
),
m(
dt
)+1),
qr,
"Q"&ROUNDUP(
mo/3,
0
),
gr,
GROUPBY(
HSTACK(
qr,
mo
),
TAKE(
data,
,
-1
),
SUM
),
grM,
HSTACK(
gr,
MAP(
INDEX(
gr,
,
2
),
LAMBDA(
a,
IF(
a<>"",
TEXT(
a*28,
"mmm"
),
""
)
)
)
),
res,
VSTACK(
{"Quarter",
"Month",
"Payment"},
CHOOSECOLS(
grM,
{1,
4,
3}
)
),
res)Excel solution 20 for Quarterly Payment Grouping, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(f,
A2:A101,
d,
IF(DAY(
f
)>25,
f+10*(YEAR(
f
)=YEAR(
f+10
)),
f),
REGEXREPLACE(
GROUPBY(
HSTACK(
"Q""IENT(
MONTH(
d
),
3
)+1,
MONTH(
d
)&TEXT(
d,
"[$-en-US]mmm"
)
),
B2:B101,
SUM
),
"(dd?([A-Z]))",
"$2"
))Excel solution 21 for Quarterly Payment Grouping, proposed by Ben Warshaw:
=LET(
_Date,
$A$2:$A$101,
_Date2,
A2,
_Payment,
$B$2:$B$101,
_Step1,
SEQUENCE(
MAX(
_Date
) - MIN(
_Date
) + 1,
1,
_Date2,
1
),
_Step2,
SCAN(
1,
_Step1,
LAMBDA(
s,
c,
IF(
DAY(
c
) = 26,
s + 1,
s
)
)
),
_Step3,
SUMIFS(
_Payment,
_Date,
_Step1
),
_Step4,
GROUPBY(
_Step2,
_Step3,
SUM,
,
),
_Step5,
IFERROR(
TEXT(
DATE(
2000,
TAKE(
_Step4,
,
1
),
1
),
"MMM"
),
""
),
_Step6,
IFERROR(
"Q" & CEILING(
TAKE(
_Step4,
,
1
) / 3,
1
),
"Total"
),
_Result,
HSTACK(
_Step6,
_Step5,
TAKE(
_Step4,
,
-1
)
),
_Result
)Excel solution 22 for Quarterly Payment Grouping, proposed by O. Zini:
=LET(
co_date;MAP(D2:D101;LAMBDA(_date;IF(AND(DAY(_date)>=26;MONTH(_date)<=11);EOMONTH(_date;1);_date)));
main;
GROUPBY(HSTACK("Q"&CEILING(MONTH(co_date);3)/3;MONTH(co_date);TEXT(co_date;"[$-en-US]mmmm"));E2:E101;SUM;;;2);
CHOOSECOLS(main;{1;3;4})
)Solving the challenge of Quarterly Payment Grouping with Python
Python solution 1 for Quarterly Payment Grouping, proposed by Konrad Gryczan, PhD:
import pandas as pd
from pandas.tseries.offsets import DateOffset
path = "676 Credit card payment amount.xlsx"
input_df = pd.read_excel(path, usecols="A:B", nrows=101)
test_df = pd.read_excel(path, usecols="E:G", nrows=14)
input_df['Date'] = pd.to_datetime(input_df['Date'])
input_df['adjusted_date'] = input_df['Date'] + pd.to_timedelta((input_df['Date'].dt.day >= 26) * 6, unit='D')
input_df = input_df[input_df['adjusted_date'].dt.year == 2025]
result = (input_df
.assign(Quarter="Q" + input_df['adjusted_date'].dt.quarter.astype(str),
Month=input_df['adjusted_date'].dt.strftime('%b'),
MonthOrder=input_df['adjusted_date'].dt.month)
.groupby(['Quarter', 'Month', 'MonthOrder'], as_index=False)
.agg(Payment=('Payment', 'sum'))
.sort_values(by=['Quarter', 'MonthOrder'])
.drop(columns=['MonthOrder']))
total = pd.DataFrame([{'Quarter': 'Total', 'Month': None, 'Payment': result['Payment'].sum()}])
result = pd.concat([result, total], ignore_index=True)
print(result)
Python solution 2 for Quarterly Payment Grouping, proposed by Anshu Bantra:
from dateutil.relativedelta import relativedelta
df = to_df(REF("A1:B101"))
df['Date'] = pd.to_datetime(df['Date'])
df['new'] = np.where(
df['Date'].dt.day > 25,
df['Date'].apply(lambda x: (x + relativedelta(months=1)).replace(day=1)),
df['Date']
)
df['Year'] = df['new'].dt.year
df['Month'] = df['new'].dt.month
df['Qtr'] = ( ((df['Month']-1)%12) // 3) + 1
df.groupby(by=['Year', 'Qtr', 'Month'])
['Payment'].sum()
.reset_index()
.sort_values(by=['Year', 'Qtr', 'Month'])
Solving the challenge of Quarterly Payment Grouping with Python in Excel
Python in Excel solution 1 for Quarterly Payment Grouping, proposed by Alejandro Campos:
df = xl("A1:B101", headers=True)
df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%Y')
df['adjusted_date'] = df['Date'] + pd.to_timedelta((df['Date'].dt.day >= 26) * 6, unit='D')
df = df.query('adjusted_date.dt.year == 2025').assign(
Quarter="Q" + df['adjusted_date'].dt.quarter.astype(str),
Month=df['adjusted_date'].dt.strftime('%b'),
MonthOrder=df['adjusted_date'].dt.month)
grouped = df.groupby(['Quarter', 'Month', 'MonthOrder'])['Payment'].sum().reset_index().sort_values('MonthOrder').drop('MonthOrder', axis=1)
result = pd.concat([grouped, pd.DataFrame({'Quarter': ['Total'], 'Month': [''], 'Payment': [grouped['Payment'].sum()]})], ignore_index=True)
Solving the challenge of Quarterly Payment Grouping with R
R solution 1 for Quarterly Payment Grouping, proposed by Hussein SATOUR:
=LET(a,A2:A101+6,b,IF((a>--"28/2")*(a<--"3/3"),--"28/2",a),c,MONTH(b),DROP(GROUPBY(HSTACK(YEAR(b),c,"Q"&ROUNDUP(c/3,0),TEXT(b,"mmm")),B2:B101,SUM),,2))&&