List the last Sundays of the all 12 months of year given in cell A2.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 653
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Last Sundays Of Year with Power Query
_x000D_Power Query solution 2 for Last Sundays Of Year, proposed by Vida Vaitkunaite:
let
Year = Excel.CurrentWorkbook(){[Name="Year"]}[Content],
Answer = List.Transform({1..12}, (x)=> let
a = Date.EndOfMonth(hashtag#date(Year{0}[Column1], x, 1)),
b = Date.DayOfWeek(a, Day.Sunday),
c = if b=0 then a else a - hashtag#duration(b,0,0,0)
in c)
in
Answer
Power Query solution 3 for Last Sundays Of Year, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.AddColumn(Source, "A", each
let
a = hashtag#date([Column1],1,1),
b = hashtag#date([Column1],12,31),
c = List.Transform({Number.From(a)..Number.From(b)}, Date.From),
d = List.Transform({1..12}, each List.Last(List.Select(List.Select(c,
(x)=> Date.Month(Date.From(x))=_), (y)=> Date.DayOfWeek(y)=0 )))
in d)[A]{0}
in
Sol
Power Query solution 4 for Last Sundays Of Year, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = List.Transform(
{1 .. 12},
each [
a = hashtag#date(Source[Column1]{0}, _, 1),
b = List.Dates(a, Date.DaysInMonth(a), Duration.From(1)),
c = List.Last(List.Select(b, (x) => Date.DayOfWeekName(x) = "Sunday"))
][c]
),
FromCols = Table.FromColumns(Transform, {"Answer Expected"}),
Result = Table.TransformColumnTypes(FromCols, {"Answer Expected", type date})
in
Result
Power Query solution 6 for Last Sundays Of Year, proposed by Ramiro Ayala Chávez:
let
y = 2025,
a = {Number.From(hashtag#date(y,1,1))..Number.From(hashtag#date(y,12,31))},
b = Table.FromColumns({List.Transform(a,Date.From)}),
c = Table.AddColumn(b,"D", each Date.DayOfWeekName([Column1])),
d = Table.Group(c,"Column1",{"G", each _},0,(x,y)=>Value.Compare(Date.Month(x),Date.Month(y)))[G],
e = List.Transform(d, each Table.LastN(Table.SelectRows(_, each [D]="Sunday"),1)[Column1]),
f = List.Transform(List.Combine(e), each Text.Remove(Text.Proper(Date.ToText(_,[Format="dd-MMM-yy"])),".")),
Sol = Table.FromColumns({f},{"Answer Expected"})
in
Sol
Power Query solution 7 for Last Sundays Of Year, proposed by Seokho MOON:
let
last_sunday = (year) => List.Transform({1..12}, each Fun(year, _)),
Fun = (year, month) => [
A = Date.EndOfMonth(hashtag#date(year, month, 1)),
B = Date.DayOfWeek(A, 0),
C = Date.AddDays(A, -B)
][C]
in
last_sunday(2025)
Power Query solution 8 for Last Sundays Of Year, proposed by Meganathan Elumalai:
let
Source = 2025,
Result = Table.FromColumns({List.Transform({1..12}, each [eom = Date.EndOfMonth(hashtag#date(Source,_,1)), fin = eom - Duration.From(Date.DayOfWeek(eom))][fin])},{"Result"})
in
Result
Power Query solution 9 for Last Sundays Of Year, proposed by Peter Krkos:
let
Year = 2025,
Gen_LastSudays = Table.FromList(List.Transform({1..12}, each
let EOM = Date.EndOfMonth(hashtag#date(Year, _, 1))
in List.Select(List.Dates(Date.AddDays(EOM, -6),7,hashtag#duration(1,0,0,0)),
(x)=> Date.DayOfWeek(x, Day.Sunday) = 0){0}), (x)=> {x}, type table[Last Sunday=date])
in
Gen_LastSudays
Power Query solution 10 for Last Sundays Of Year, proposed by Krzysztof Kominiak:
let
LastSundays = Table.FromColumns( {List.TransformMany( {1..12}, (x)=> {hashtag#date( Excel.CurrentWorkbook(){[Name="Table1"]}[Content][Column1]{0} ,x,1 )}, (x,y)=> Date.AddDays( Date.EndOfMonth(y),-Date.DayOfWeek( Date.EndOfMonth(y), Day.Sunday )) )},{ "Last Sundays" } )
in
LastSundays
Power Query solution 11 for Last Sundays Of Year, proposed by Krzysztof Kominiak:
let
LastSundays = Table.FromColumns(
{List.Transform( {1..12}, (m) =>
let
FDay = hashtag#date( Excel.CurrentWorkbook(){[Name="Table1"]}[Content][Column1]{0} , m, 1),
EoM = Date.EndOfMonth( FDay ),
Offset = Date.DayOfWeek( EoM, Day.Sunday ),
LSun = Date.AddDays( EoM, -Offset )
in
LSun )} , {"Last Sundays"} )
in
LastSundays
Solving the challenge of Last Sundays Of Year with Excel
_x000D_Excel solution 1 for Last Sundays Of Year, proposed by Bo Rydobon 🇹🇭:
=WORKDAY.INTL(EDATE(1&-A2,SEQUENCE(12)),-1,"1111110")
=LET(d,EDATE(1&-A2,SEQUENCE(12)),d-WEEKDAY(d,2))Excel solution 2 for Last Sundays Of Year, proposed by Rick Rothstein:
=LET(
d,
DATE(
A2,
SEQUENCE(
12,
,
2
),
7
),
d-WEEKDAY(
d
)-6
)Excel solution 3 for Last Sundays Of Year, proposed by John V.:
=LET(
i,
DATE(
A2,
ROW(
2:13
),
),
i-MOD(
i-1,
7
)
)Excel solution 4 for Last Sundays Of Year, proposed by Kris Jaganah:
=LET(a,SEQUENCE(366,,DATE(A2,12,31),-1),DROP(GROUPBY(MONTH(a),a,SINGLE,,0,,WEEKDAY(a)=1),,1))Excel solution 5 for Last Sundays Of Year, proposed by Julian Poeltl:
=LET(
Y,
A2,
L,
EOMONTH(
DATE(
Y,
1,
1
),
SEQUENCE(
12,
,
0
)
),
L-WEEKDAY(
L,
1
)+1
)Excel solution 6 for Last Sundays Of Year, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_year,
A2,
_eom,
DATE(
_year,
SEQUENCE(
12,
,
2
),
0
),
_wkdy,
WEEKDAY(
_eom,
1
),
_rtrn,
_eom - _wkdy + 1,
_rtrn
)Excel solution 7 for Last Sundays Of Year, proposed by Timothée BLIOT:
=LET(A,EOMONTH(DATE(2025,SEQUENCE(12),1),0),TEXT(A-WEEKDAY(A)+1,"DD-MMM-YY"))Excel solution 8 for Last Sundays Of Year, proposed by Hussein SATOUR:
=LET(
a,
EOMONTH(
SEQUENCE(
12
)&"/"&A2,
0
),
a-WEEKDAY(
a
)+1
)Excel solution 9 for Last Sundays Of Year, proposed by Oscar Mendez Roca Farell:
=LET(i,EOMONTH(ROW(1:12)&-A2,0),i-WEEKDAY(i)+1)Excel solution 10 for Last Sundays Of Year, proposed by Sunny Baggu:
=MAP(
SEQUENCE(
12
),
LAMBDA(
m,
LET(
_a,
EOMONTH(
DATE(
A2,
m,
1
),
0
) -
SEQUENCE(
7,
,
0
),
_b,
WEEKDAY(
_a
),
FILTER(
_a,
_b = 1
)
)
)
)Excel solution 11 for Last Sundays Of Year, proposed by Abdallah Ally:
=LET(y,A2,MAP(SEQUENCE(12),LAMBDA(x,LET(a,TOCOL(DATE(y,x, SEQUENCE(31)),2),MAX(FILTER(a,TEXT(a,"ddd")="Sun"))))))Excel solution 12 for Last Sundays Of Year, proposed by Anshu Bantra:
=LET(
year_dates_,
SEQUENCE(
365,
,
DATE(
2025,
1,
1
)
),
sundays_,
FILTER(
year_dates_,
TEXT(
year_dates_,
"ddd"
) = "Sun"
),
DROP(
GROUPBY(
EOMONTH(
--sundays_,
0
),
sundays_,
MAX,
0
),
,
1
)
)Excel solution 13 for Last Sundays Of Year, proposed by 🇵🇪 Ned Navarrete C.:
=LET(
f,
EOMONTH(
DATE(
A2,
ROW(
1:12
),
1
),
0
),
f-MOD(
WEEKDAY(
f,
2
),
7
)
)Excel solution 14 for Last Sundays Of Year, proposed by Md. Zohurul Islam:
=LET(a,
DATE(
A2,
1,
1
)+SEQUENCE(
366,
,
0
),
b,
FILTER(HSTACK(
MONTH(
a
),
a
),
(TEXT(
a,
"ddd"
)="Sun")*YEAR(
a
)),
c,
DROP(
GROUPBY(
DROP(
b,
,
-1
),
DROP(
b,
,
1
),
MAX,
0,
0
),
,
1
),
c)Excel solution 15 for Last Sundays Of Year, proposed by Pieter de B.:
=LET(d,DATE(A2,SEQUENCE(12,,2),),d-MOD(d-1,7))Excel solution 16 for Last Sundays Of Year, proposed by Hamidi Hamid:
=WORKDAY.INTL(EOMONTH(DATE(A2;SEQUENCE(12);1);0)+1;-1;"1111110")Excel solution 17 for Last Sundays Of Year, proposed by Asheesh Pahwa:
=LET(e,EOMONTH(DATE(A2,SEQUENCE(12),1),0),w,WEEKDAY(e,2),IF(w=7,e,e-w))Excel solution 18 for Last Sundays Of Year, proposed by ferhat CK:
=LET(y,
SEQUENCE(
10^5
),
z,
TOCOL(IF((YEAR(
y
))*(MOD(
y,
7
)=1)=A2,
y,
1/0),
2),
TAKE(
GROUPBY(
MONTH(
z
),
z,
MAX,
,
0
),
,
-1
))Excel solution 19 for Last Sundays Of Year, proposed by Jaroslaw Kujawa:
=DROP(REDUCE("";SEQUENCE(12);LAMBDA(a;x;LET(z;SEQUENCE(7;;DATE(A2;x+1;0);-1);zz;HSTACK(z;WEEKDAY(z));VSTACK(a;FILTER(TAKE(zz;;1);TAKE(zz;;-1)=1)))));1)Excel solution 20 for Last Sundays Of Year, proposed by Seokho MOON:
=LET(l,DATE($A$2,SEQUENCE(12,,2),0),w,MOD(WEEKDAY(l,2),7), l-w)Excel solution 21 for Last Sundays Of Year, proposed by Andy Heybruch:
=LET(_d,SEQUENCE(365,,DATE(A1,1,1)),
XLOOKUP(SEQUENCE(12)&"|1",MONTH(_d)&"|"&WEEKDAY(_d),_d,,,-1))Excel solution 22 for Last Sundays Of Year, proposed by Meganathan Elumalai:
=LET(a,EOMONTH(DATE(A2,1,1),SEQUENCE(12,,0)),a-WEEKDAY(a,1)+1)Excel solution 23 for Last Sundays Of Year, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(x,
SEQUENCE(
365,
,
DATE(
2025,
1,
1
),
1
),
MAP(UNIQUE(
MONTH(
x
)
),
LAMBDA(y,
MAX(FILTER(x,
((y=MONTH(
x
))*(TEXT(
x,
"ddd"
)="sun"))>0)))))Excel solution 24 for Last Sundays Of Year, proposed by Imam Hambali:
=LET(
y,
A2,
d,
SEQUENCE(
365,
,
y&"-01-01"
),
df,
FILTER(
d,
TEXT(
d,
"dddd"
)="Sunday"
),
TAKE(
GROUPBY(
TEXT(
& df,
"yyyy-mm"
),
df,
MAX,
0,
0
),
,
-1
)
)Excel solution 25 for Last Sundays Of Year, proposed by Mey Tithveasna:
=LET(_date,EDATE("1-"&A2,SEQUENCE(12)),_date-WEEKDAY(_date,2))Excel solution 26 for Last Sundays Of Year, proposed by Philippe Brillault:
=LET(
d,
DATE(
A2,
SEQUENCE(
12
)+1,
),
d-MOD(
d-1,
7
)
)Excel solution 27 for Last Sundays Of Year, proposed by El Badlis Mohd Marzudin:
=LET(a,
SEQUENCE(
365,
,
DATE(
A2,
1,
1
)
),
b,
WEEKDAY(
a
),
c,
MONTH(
a
),
MAP(UNIQUE(
c
),
LAMBDA(x,
MAX(FILTER(a,
(b=1)*(c=x))))))Excel solution 28 for Last Sundays Of Year, proposed by Erdit Qendro:
=LET(yr,A2,mo,SEQUENCE(12,),
moEnd,EOMONTH(DATE(yr,mo,1),0),
lastDay,WEEKDAY(moEnd,1),
dalastsun,MOD(lastDay-1,7),
moEnd-dalastsun)
It works for 2025, it should do for other years too!(I am confident of it! 🤨)Excel solution 29 for Last Sundays Of Year, proposed by Ernesto Vega Castillo:
=LET(
a,
A2,
s,
SEQUENCE(
12
),
d,
DATE(
a,
s+1,
1
),
d-WEEKDAY(
d,
2
)
)Excel solution 30 for Last Sundays Of Year, proposed by Gabriel Pugliese:
=LET(d,SEQUENCE(365,,DATE(2025,1,1)),
TEXT(TAKE(GROUPBY(MONTH(d),d,LAMBDA(x,TEXTAFTER(TEXTJOIN("|",,x),"|",-1)),,0,,WEEKDAY(d,1)=1),,-1),"dd-mmm-yy"))Excel solution 31 for Last Sundays Of Year, proposed by Stefan Alexandrov:
=LET(_data,
SEQUENCE(365,
1,
(DATE(
A2,
1,
1
)),
1),
_sundays,
FILTER(
_data,
WEEKDAY(
_data,
2
)=7
),
_months,
MONTH(
_sundays
),
TEXT(
DROP(
GROUPBY(
_months,
_sundays,
MAX,
0,
0
),
,
1
),
"dd-mmm-yy"
)
)Excel solution 32 for Last Sundays Of Year, proposed by abdelaziz kamal allam:
=MAP(--(1&"-"&SEQUENCE(
12
)&"-"&2025),
LAMBDA(
k,
LET(
x,
SEQUENCE(
EDATE(
k,
1
)-k,
,
k,
1
),
MAX(
FILTER(
x,
WEEKDAY(
x,
1
)=1
)
)
)
))Excel solution 33 for Last Sundays Of Year, proposed by Fredrick Nwanyanwu:
=LET(
d,
EOMONTH(
DATE(
A2,
SEQUENCE(
12,
1
),
1
),
0
),
s,
d-WEEKDAY(
d,
1
)+1,
s
)Excel solution 34 for Last Sundays Of Year, proposed by Casper Badenhorst:
=TEXT(EOMONTH(
DATE(
$A$2,
SEQUENCE(
12
),
),
1
)-(WEEKDAY(
EOMONTH(
DATE(
$A$2,
SEQUENCE(
12
),
),
1
)-1
)),
"dd-mmm-yy")Excel solution 35 for Last Sundays Of Year, proposed by David Wasserman:
=LET(yr,A2,s,SEQUENCE(12),d,DATE(yr,s,1),eoms,EOMONTH(d,0),eoms-WEEKDAY(eoms,17)+1)Solving the challenge of Last Sundays Of Year with Python
_x000D_Python solution 1 for Last Sundays Of Year, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "653 Last Sundays of All Months.xlsx"
test = pd.read_excel(path, usecols="C", nrows = 12)
def get_last_sundays(year):
return pd.date_range(f"{year}-01-01", f"{year}-12-31", freq="W-SUN").to_series()
.groupby(lambda x: x.month).max().reset_index(drop=True).to_frame(name="Answer Expected")
print(get_last_sundays(2025).equals(test)) # True
Python solution 2 for Last Sundays Of Year, proposed by Abdallah Ally:
import pandas as pd
# Create a function to generate last sundays
# for each month of a given year
def get_last_sundays(year):
sundays = pd.date_range(f'{year}-01-01', f'{year}-12-31', freq='W-SUN')
last_sundays = sundays.to_series().groupby(sundays.month).last()
return last_sundays
# Perform data manipulation
df = pd.DataFrame(get_last_sundays(2025), columns=['Answer Expected'])
df
Python solution 3 for Last Sundays Of Year, proposed by Anshu Bantra:
import calendar, datetime
def get_last_sundays(year):
last_sundays = []
for month in range(1, 13):
last_day = calendar.monthrange(year, month)[1]
for day in range(last_day, 0, -1):
if calendar.weekday(year, month, day) == calendar.SUNDAY:
last_sundays.append(datetime.date(year, month, day).strftime('%d-%b-%y'))
break
return last_sundays
get_last_sundays(2025)
Solving the challenge of Last Sundays Of Year with Python in Excel
_x000D_Python in Excel solution 1 for Last Sundays Of Year, proposed by Alejandro Campos:
import calendar
from datetime import datetime, timedelta
def last_sundays(year):
return [(datetime(year, m, calendar.monthrange(year, m)[1]) - timedelta(
days=(datetime(year, m, calendar.monthrange(year, m)[1]).weekday(
) - 6) % 7)).strftime('%d-%b-%y') for m in range(1, 13)]
last_sundays_2025 = last_sundays(xl("A2"))
Python in Excel solution 2 for Last Sundays Of Year, proposed by Aditya Kumar Darak 🇮🇳:
year = 2025
dates = pd.date_range(f"{year}-01-01", f"{year}-12-31", freq="M")
offset = (dates.weekday - 6) % 7
result = dates - pd.to_timedelta(offset, unit="d")
result
Solving the challenge of Last Sundays Of Year with R
_x000D_R solution 1 for Last Sundays Of Year, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(glue)
year = 2025
path = "Excel/653 Last Sundays of All Months.xlsx"
test25 = read_excel(path, range = "C1:C13")
result = seq(as.Date(paste0(year, "-01-01")), as.Date(paste0(year, "-12-31")), by = "days") %>%
keep(~ wday(.x, week_start = 1) == 7) %>%
tibble(date = .) %>%
mutate(month = month(date)) %>%
summarise(last_sunday = max(date, na.rm = T) %>% as.POSIXct(), .by = month)
all.equal(test25$`Answer Expected`, result$last_sunday, check.attributes = F)
#> [1] TRUE