List the count of dates along with Min and Max date for the rows in columns A & B where MM + DD = YY. (Dates given are in MDY format) For example : 12/13/2025 where 12+13 = 25 which is equal to YY part of the year which is 25.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 199
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sum-Month Equals Year Dates with Power Query
Power Query solution 1 for Sum-Month Equals Year Dates, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.FromRecords(
Table.TransformRows(
Source,
each
let
l = List.Select(
{Number.From([From Date]) .. Number.From([To Date])},
each
let
d = Date.From(_)
in
Date.Day(d) + Date.Month(d) = Number.Mod(Date.Year(d), 100)
)
in
[
Count = List.Count(l),
Min Date = Date.From(List.Min(l)),
Max Date = Date.From(List.Max(l))
]
)
)
in
AnsPower Query solution 2 for Sum-Month Equals Year Dates, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
Solution = Table.FromRows(
List.Transform(
Table.ToRows(Source),
each
let
l = (
List.Select(
List.Transform({Number.From(_{0}) .. Number.From(_{1})}, each Date.From(_)),
each Date.Month(_) + Date.Day(_) = Number.Mod(Date.Year(_), 100)
)
)
in
{List.Count(l), l{0}, List.Max(l)}
),
{"Count", "Min Date", "Max Date"}
)
in
SolutionPower Query solution 3 for Sum-Month Equals Year Dates, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
Record = Table.AddColumn (
Source,
"R",
each [
L = List.Dates (
Date.From ( [From Date] ),
Number.From ( [To Date] - [From Date] ) + 1,
hashtag#duration ( 1, 0, 0, 0 )
),
S = List.Select (
L,
( f ) => Date.Month ( f ) + Date.Day ( f ) + 2000 = Date.Year ( f )
),
R = [ Count = List.Count ( S ), Min = List.Min ( S ), Max = List.Max ( S ) ]
][R]
),
Return = Table.FromRecords ( Record[R] )
in
Return
Power Query solution 4 for Sum-Month Equals Year Dates, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(
Source,
"Custom",
each
let
a = {Number.From([From Date]) .. Number.From([To Date])},
b = List.Transform(a, each Date.From(_)),
c = List.Select(b, each Date.Day(_) + Date.Month(_) = Number.From(Date.ToText(_, "yy"))),
d = [Count = List.Count(c), Min Date = List.Min(c), Max Date = List.Max(c)]
in
d
)[Custom],
Sol = Table.FromRecords(AddCol)
in
SolPower Query solution 5 for Sum-Month Equals Year Dates, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ChangeType = Table.TransformColumnTypes(
Source,
{{"To Date", type date}, {"From Date", type date}}
),
DatesTable = Table.AddColumn(
ChangeType,
"DateList",
each {Number.From([From Date]) .. Number.From([To Date])}
),
Expand = Table.ExpandListColumn(DatesTable, "DateList"),
ReType = Table.TransformColumnTypes(Expand, {{"DateList", type date}}),
#"AddMM+DD" = Table.AddColumn(ReType, "MM+DD", each Date.Month([DateList]) + Date.Day([DateList])),
AddYY = Table.AddColumn(#"AddMM+DD", "YY", each Number.From(Text.End(Date.ToText([DateList]), 2))),
FilterEqual = Table.SelectRows(
Table.AddColumn(AddYY, "Equal", each if [#"MM+DD"] = [YY] then 1 else 0),
each [Equal] = 1
),
Group = Table.Group(
FilterEqual,
{"From Date", "To Date"},
{
{"Count", each Table.RowCount(_), Int64.Type},
{"Min Date", each List.Min([DateList]), type nullable date},
{"Max Date", each List.Max([DateList]), type nullable date}
}
)
in
GroupSolving the challenge of Sum-Month Equals Year Dates with Excel
Excel solution 1 for Sum-Month Equals Year Dates, proposed by John V.:
=MAKEARRAY(ROWS(A2:B6),3,LAMBDA(r,c,LET(t,INDEX(A2:A6,r),s,SEQUENCE(1+INDEX(B2:B6,r)-t,,t),d,FILTER(s,DAY(s)+MONTH(s)=--TEXT(s,"y")),CHOOSE(c,ROWS(d),@d,MAX(d)))))Excel solution 2 for Sum-Month Equals Year Dates, proposed by محمد حلمي:
=TEXTSPLIT(CONCAT(MAP(A2:A6,B2:B6,LAMBDA(a,b,LET(d,SEQUENCE(b-a+1,,a),
r,FILTER(d,DAY(d)+MONTH(d)=YEAR(d)-2000),CONCAT(HSTACK(ROWS(r),MIN(r),MAX(r))&" "))))&"-")," ","-",1)Excel solution 3 for Sum-Month Equals Year Dates, proposed by محمد حلمي:
=REDUCE(HSTACK("Count","Min Date","Max Date"),A2:A6,LAMBDA(a,v,LET(d,SEQUENCE(OFFSET(v,,1)-v+1,,v),
r,FILTER(d,DAY(d)+MONTH(d)=YEAR(d)-2000),VSTACK(a,HSTACK(ROWS(r),MIN(r),MAX(r))))))Excel solution 4 for Sum-Month Equals Year Dates, proposed by Kris Jaganah:
=DROP(REDUCE(0,MAP(A2:A6,B2:B6,LAMBDA(x,y,LET(a,TEXT(SEQUENCE(y-x+1,,x),"dd/mm/yyyy"),b,--(--LEFT(a,2)+MID(a,4,2)=--RIGHT(a,2)),c,FILTER(DATEVALUE(a),b),TEXTJOIN("#",1,COUNT(c),TEXT(MIN(c),"dd/mm/yyyy"),TEXT(MAX(c),"dd/mm/yyyy"))))),LAMBDA(v,w,VSTACK(v,--TEXTSPLIT(w,"#")))),1)Excel solution 5 for Sum-Month Equals Year Dates, proposed by Julian Poeltl:
=LET(T,A2:B6,REDUCE(HSTACK("Count","Min Date","Max Date"),SEQUENCE(ROWS(T)),LAMBDA(A,B,VSTACK(A,LET(S,SEQUENCE(INDEX(T,B,2)-INDEX(T,B,1)+1,,INDEX(T,B,1)),C,MONTH(S)+DAY(S)=1*(RIGHT(YEAR(S),2)),F,FILTER(S,C),HSTACK(ROWS(F),TAKE(F,1),TAKE(F,-1)))))))Excel solution 6 for Sum-Month Equals Year Dates, proposed by Timothée BLIOT:
=REDUCE({"Count","Min Date","Max Date"},A2:A6, LAMBDA(acc,x, LET(A,SEQUENCE(XLOOKUP(x,A2:A6,B2:B6)-x+1,,x), B,--((MONTH(A)+DAY(A))=(1*TEXT(A,"yy"))), R,HSTACK(SUM(B),MIN(FILTER(A,B)),MAX(FILTER(A,B))), VSTACK(acc,R))))
Textsplit version: =TEXTSPLIT(TEXTJOIN("/",,MAP(A2:A6,B2:B6,LAMBDA(x,y,LET(A,SEQUENCE(y-x+1,,x),B,--((MONTH(A)+DAY(A))=(1*TEXT(A,"yy"))),TEXTJOIN (",",,SUM(B),MIN(FILTER(A,B)),MAX(FILTER(A,B))))))),",","/")Excel solution 7 for Sum-Month Equals Year Dates, proposed by Hussein SATOUR:
=--TEXTSPLIT(CONCAT(BYROW(A2:B6, LAMBDA(x, LET(d, SEQUENCE(MAX(x)-MIN(x)+1,,MIN(x)), f, FILTER(d, DAY(d)+MONTH(d)=--TEXT(d, "yy")), TEXTJOIN(",",,COUNT(f), MIN(f), MAX(f), "/"))))),",","/",1)Excel solution 8 for Sum-Month Equals Year Dates, proposed by Duy Tùng:
=REDUCE(D1:F1,B2:B6,LAMBDA(x,y,LET(a,ROW(INDIRECT(@+A6:y&":"&y)),b,TOCOL(a/(MONTH(a)+DAY(a)=--TEXT(a,"yy")),3),VSTACK(x,HSTACK(COUNT(b),MIN(b),MAX(b))))))Excel solution 9 for Sum-Month Equals Year Dates, proposed by Sunny Baggu:
=DROP(
REDUCE(
"Thanks EXcel BI🌻",
SEQUENCE(ROWS(A2:A6)),
LAMBDA(a, v,
VSTACK(
a,
LET(
_list, SEQUENCE(INDEX(B2:B6, v, 1) - INDEX(A2:A6, v, 1) + 1, , INDEX(A2:A6, v, 1)),
_day, DAY(_list),
_month, MONTH(_list),
_year, YEAR(_list),
_cond, _day + _month - RIGHT(_year, 2) * 1,
_flist, FILTER(_list, _cond = 0),
HSTACK(ROWS(_flist), MIN(_flist), MAX(_flist))
)
)
)
),
1
)Excel solution 10 for Sum-Month Equals Year Dates, proposed by Md. Zohurul Islam:
=LET(u,A2:A6,v,B2:B6,w,HSTACK("Count","Min Date","Max Date"),
p, MAP(u,v,LAMBDA(x,y,LET(a,SEQUENCE(y-x+1,,x),b,(DAY(a)+MONTH(a))=TEXT(a,"yy")+0,d,FILTER(a,b),e,TEXTJOIN("-",,COUNT(d),MIN(d),MAX(d)),e))),
q,REDUCE(w,p,LAMBDA(x,y,VSTACK(x,--TEXTSPLIT(y,"-")))),
q)Excel solution 11 for Sum-Month Equals Year Dates, proposed by Pieter de B.:
=--TEXTSPLIT(TEXTJOIN(";",1,(MAP(A2:A6,B2:B6,LAMBDA(a,b,LET(d,SEQUENCE(1+b-a,,a),f,FILTER(d,MONTH(d)+DAY(d)=--TEXT(d,"yy")),TEXTJOIN(",",1,COUNT(f),TAKE(f,1),TAKE(f,-1))))))),",",";") not sure to use min/max versus take. I expect take to calculate faster, but haven't tested (using phone)Excel solution 12 for Sum-Month Equals Year Dates, proposed by Dhaval Patel:
=SUMPRODUCT(--(MONTH(ROW(INDIRECT(A2 & ":" & B2))) + DAY(ROW(INDIRECT(A2 & ":" & B2))) = MOD(YEAR(ROW(INDIRECT(A2 & ":" & B2))),100)))
Formula for cell E2
=MIN(IF(MONTH(ROW(INDIRECT(A2 & ":" & B2))) + DAY(ROW(INDIRECT(A2 & ":" & B2))) = MOD(YEAR(ROW(INDIRECT(A2 & ":" & B2))),100), ROW(INDIRECT(A2 & ":" & B2))))
Formula for cell F2
=MAX(IF(MONTH(ROW(INDIRECT(A2 & ":" & B2))) + DAY(ROW(INDIRECT(A2 & ":" & B2))) = MOD(YEAR(ROW(INDIRECT(A2 & ":" & B2))),100), ROW(INDIRECT(A2 & ":" & B2))))Excel solution 13 for Sum-Month Equals Year Dates, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=HSTACK(MAP(A2:A6;B2:B6;LAMBDA(a;b;LET(x;SEQUENCE(b-a+1;;a;1);COUNTA(FILTER(IF(DAY(x)+MONTH(x)=VALUE(RIGHT(YEAR(x);2));x;"");IF(DAY(x)+MONTH(x)=VALUE(RIGHT(YEAR(x);2));x;"")<>"")))));MAP(A2:A6;B2:B6;LAMBDA(x;y;LET(p;SEQUENCE(y-x+1;;x;1);MIN(IF(DAY(p)+MONTH(p)=VALUE(RIGHT(YEAR(p);2));p;"")))));MAP(A2:A6;B2:B6;LAMBDA(m;n;LET(z;SEQUENCE(n-m+1;;m;1);MAX(IF(DAY(z)+MONTH(z)=VALUE(RIGHT(YEAR(z);2));z;""))))))Excel solution 14 for Sum-Month Equals Year Dates, proposed by Guillermo Arroyo:
=--TEXTSPLIT(TEXTJOIN("#",,LET(d,SEQUENCE(31),m,SEQUENCE(,12),f,SORT(TOCOL(DATEVALUE(m&"-"&d&"-"&2000+d+m),3)),MAP(A2:A6,B2:B6,LAMBDA(a,b,LET(n,FILTER(f,(f>=a)*(f<=b)),TEXTJOIN("|",,COUNT(n),MIN(n),MAX(n))))))),"|","#")Excel solution 15 for Sum-Month Equals Year Dates, proposed by Lorenzo Foti:
=+LET(
date1;A2;
date2;B2;
listLng;B2-A2;
dates;date1+SEQUENCE(listLng);
checks;IF(DAY(dates)+MONTH(dates)=NUMBERVALUE(RIGHT(YEAR(dates);2));1;0);
cnt;SUM(checks);
minDate;TAKE(FILTER(dates;checks=1);1);
maxDate;TAKE(FILTER(dates;checks=1);-1);
outP;HSTACK(cnt;minDate;maxDate);
outP)Solving the challenge of Sum-Month Equals Year Dates with Python in Excel
Python in Excel solution 1 for Sum-Month Equals Year Dates, proposed by Alejandro Campos:
from datetime import timedelta
df = xl("A1:B6", headers=True)
df['From Date'] = pd.to_datetime(df['From Date'], format='%d/%m/%Y')
df['To Date'] = pd.to_datetime(df['To Date'], format='%d/%m/%Y')
def filter_dates(row):
from_date = row['From Date']
to_date = row['To Date']
current_date = from_date
valid_dates = []
while current_date <= to_date:
day = current_date.day
month = current_date.month
year = current_date.year % 100
if day + month == year:
valid_dates.append(current_date)
current_date += timedelta(days=1)
return valid_dates
results = df.apply(filter_dates, axis=1)
count_list = []
min_date_list = []
max_date_list = []
for date_list in results:
if date_list:
count_list.append(len(date_list))
min_date_list.append(min(date_list).strftime('%d/%m/%Y'))
max_date_list.append(max(date_list).strftime('%d/%m/%Y'))
else:
count_list.append(0)
min_date_list.append(None)
max_date_list.append(None)
result_df = pd.DataFrame({
'Count': count_list,
'Min Date': min_date_list,
'Max Date': max_date_list
})
result_df
&&&