List the months and years having 5 Fridays, 5 Saturdays and 5 Sundays in a month for the years 2000 to 2999.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 541
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Five-Weekend Month Finder with Power Query
Power Query solution 1 for Five-Weekend Month Finder, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = List.Generate (
() => hashtag#date ( 2000, 01, 01 ),
each Date.Year ( _ ) < 3000,
each Date.AddMonths ( _, 1 ),
each
if Date.DayOfWeek ( _ ) = 5 and Date.DaysInMonth ( _ ) = 31 then
Date.ToText ( _, "MMM-yyy" )
else
null
),
Split = List.Split ( Source, 12 ),
Table = Table.FromList ( Split, each { Text.Combine ( _, ", " ) }, type table [ Months = text ] ),
Return = Table.SelectRows ( Table, each [Months] <> "" )
in
Return
Power Query solution 2 for Five-Weekend Month Finder, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = List.Transform({Number.From(hashtag#date(2000,01,01))..Number.From(hashtag#date(2999,12,31))}, Date.From),
Rows = Table.FromColumns({List.Select(Source, each Date.DayOfWeekName(_)="viernes" or Date.DayOfWeekName(_)="sábado" or Date.DayOfWeekName(_)="domingo")}),
Group = Table.SelectRows(Table.Group(Rows, "Column1", {{"A", each List.Count([Column1])}},
0, (a,b)=> Number.From(Date.Month(a) <> Date.Month(b))), each [A]=15)[[Column1]],
Dates = Table.TransformColumns(Group, {"Column1", each Date.ToText(_, "MMM-yyyy")}),
Sol = Table.Group(Dates, "Column1", {{"Answer", each Text.Combine([Column1], ", ")}},
0, (a,b)=> Number.From(Text.End(a,4)<>Text.End(b,4)))[[Answer]]
in
Sol
Power Query solution 3 for Five-Weekend Month Finder, proposed by Rafael González B.:
let
LP = Table.FromColumns({{2000..2999}}, {"Years"}),
MY = Table.AddColumn(LP, "Expected Answer", each
let
Y = [Years], D = hashtag#date(Y,1,1),
LD = List.Dates(D, Duration.Days(Date.EndOfYear(D) - D) + 1, hashtag#duration(1,0,0,0)),
Tbl = Table.FromColumns({LD}),
TAC = Table.AddColumn(Tbl, "Month", each Date.ToText([Column1], [Format = "MMM - yyyy", Culture = "en-US"])),
TTC = Table.TransformColumns(TAC, {"Column1", each Date.DayOfWeek(_)}),
FT = Table.SelectRows(TTC, each [Column1] >= 4 ),
GR = Table.Group(FT, {"Month"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
DT = Text.Combine(Table.SelectRows(GR, each [Count] = 15)[Month], ", ")
in
DT),
Result = Table.SelectRows(MY, each ([Expected Answer] <> ""))[[Expected Answer]]
in
Result
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Power Query solution 4 for Five-Weekend Month Finder, proposed by Glyn Willis:
let
years = {2000..2999},
months = {1..12},
crossjoin = Table.ExpandListColumn(Table.FromList(years, each {_,months},{"Year","Month"}),"Month"),
fxSelect = (_year as number,_month as number) as logical =>
let
_date = Date.EndOfWeek(hashtag#date(_year,_month,1),Day.Saturday),
days = Date.DaysInMonth(_date),
check = if Date.Day(_date)+2+(4*7) <= days then true else false
in
check,
Custom4 = Table.SelectRows(crossjoin, each fxSelect([Year],[Month])),
#"Grouped Rows" = Table.Group(Custom4, {"Year"}, {{"r", each Text.Combine(List.Transform([Month],(x)=> Record.FieldOrDefault([1="Jan",2="Feb",3="Mar",4="Apr",5="May",6="Jun",7="Jul",8="Aug",9="Sep",10="Oct",11="Nov",12="Dec"],Text.From(x)) & "-"& Text.From([Year]{0})),", "), type text}})
in
#"Grouped Rows"
Power Query solution 5 for Five-Weekend Month Finder, proposed by Ben Warshaw:
let
Source = List.Generate(
() => hashtag#date(2000, 1, 1),
each _ < hashtag#date(2999, 12, 31),
each Date.AddDays(_, 1),
each Date.From(_)
),
Counter = List.Transform(
Source,
each [
a = Date.DayOfWeek(_, 0) = Day.Friday,
b = Date.DayOfWeek(_, 0) = Day.Saturday,
c = Date.DayOfWeek(_, 0) = Day.Sunday,
d = Number.From(a) + Number.From(b) + Number.From(c)
][d]
),
YearMonth = List.Transform(
Source,
each [a = Date.ToText(_, "MMM") & "_" & Text.From(Date.Year(_))][a]
),
FromCols = Table.FromColumns({YearMonth, Counter}),
#"Grouped Rows" = Table.Group(
FromCols,
{"Column1"},
{{"Sum", each List.Sum([Column2]), type number}}
),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Sum] >= 15),
AddYear = Table.AddColumn(#"Filtered Rows", "Year", each Text.AfterDelimiter([Column1], "_"), type number),
#"Grouped Rows1" = Table.Group(AddYear, {"Year"}, {{"Count", each [Column1], type table [Column1=text]}}),
Combine = List.Transform(#"Grouped Rows1"[Count], each Text.Combine(_, ", "))
in
Combine
Solving the challenge of Five-Weekend Month Finder with Excel
Excel solution 1 for Five-Weekend Month Finder, proposed by Bo Rydobon 🇹🇭:
=TOCOL(BYROW(DATE(SEQUENCE(1000)+1999,{1,3,5,7,8,10,12},31),LAMBDA(d,ARRAYTOTEXT(FILTER(TEXT(d,"mmm-e"),WEEKDAY(d)=1)))),3)Excel solution 2 for Five-Weekend Month Finder, proposed by Bo Rydobon 🇹🇭:
=LET(
d,
EDATE(
"jan0",
SEQUENCE(
12000
)
)-1,
DROP(
GROUPBY(
YEAR(
d
),
TEXT(
d,
"mmm-e"
),
ARRAYTOTEXT,
,
0,
,
DAY(
d
)^WEEKDAY(
d
)=31
),
,
1
)
)Excel solution 3 for Five-Weekend Month Finder, proposed by Rick Rothstein:
=LET(d,
DATE(
SEQUENCE(
1000,
,
2000
),
SEQUENCE(
,
12
),
1
),
b,
BYROW(IF((WEEKDAY(
d
)=6)*(DAY(
EOMONTH(
d,
0
)
)=31),
d,
""),
LAMBDA(
r,
TEXTJOIN(
", ",
,
TEXT(
r,
"mmm-e"
)
)
)),
FILTER(
b,
b<>""
))Excel solution 4 for Five-Weekend Month Finder, proposed by John V.:
=LET(d,EDATE("jan0",ROW(1:12000)),DROP(GROUPBY(YEAR(d),TEXT(d,"mmm-e"),ARRAYTOTEXT,,0,,NETWORKDAYS.INTL(d,EOMONTH(d,0),"1111000")=15),,1))Excel solution 5 for Five-Weekend Month Finder, proposed by محمد حلمي:
=LET(i,EDATE("01/01/2000",SEQUENCE(1000*12)-1),
x,FILTER(i,186=DAY( EDATE(i,1)-1)*WEEKDAY(i)),
y,YEAR(x),MAP(UNIQUE(y),
LAMBDA(a,ARRAYTOTEXT(TEXT(FILTER(x,y=a),"mmm-e")))))Excel solution 6 for Five-Weekend Month Finder, proposed by Kris Jaganah:
=LET(
a,
DATE(
1999,
12,
31
),
b,
EOMONTH(
a,
SEQUENCE(
1001*12
)
),
c,
VSTACK(
a,
DROP(
b,
-1
)
)+1,
d,
MAP(
b,
c,
LAMBDA(
x,
y,
NETWORKDAYS.INTL(
y,
x,
"1111000"
)
)
),
DROP(
GROUPBY(
YEAR(
b
),
TEXT(
b,
"mmm-yyyy"
),
ARRAYTOTEXT,
,
0,
,
d=15
),
,
1
)
)Excel solution 7 for Five-Weekend Month Finder, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_seq,
SEQUENCE(
12 * 1000
),
_dt,
DATE(
2000,
_seq,
1
),
_ct1,
WEEKDAY(
_dt
) = 6,
_ct2,
DAY(
EOMONTH(
_dt,
0
)
) = 31,
_txt,
TEXT(
_dt,
"mmm-yyyy"
),
_yr,
YEAR(
_dt
),
_r,
GROUPBY(
_yr,
_txt,
ARRAYTOTEXT,
,
0,
,
_ct1 * _ct2
),
_r
)Excel solution 8 for Five-Weekend Month Finder, proposed by Timothée BLIOT:
=LET(V,
MAP(SEQUENCE(
1000,
,
2000
),
LAMBDA(x,
TEXTJOIN(", ",
,
MAP(ROW(
1:12
),
LAMBDA(y,
LET(A,
DATE(
x,
y,
1
),
B,
EOMONTH(
A,
0
),
S,
TEXT(
SEQUENCE(
B-A+1,
,
A
),
"ddd"
),
IF(SUM((--(S="fri"))+(--(S="sat"))+(--(S="sun")))=15,
TEXT(
A,
"mmm"
)&"-"&x,
""))))))),
FILTER(
V,
V<>""
))Excel solution 9 for Five-Weekend Month Finder, proposed by Hussein SATOUR:
=LET(a,TOCOL(ROW(1:12)&"-"&SEQUENCE(,1000,2000)),INDEX(GROUPBY(YEAR(a),TEXT(a,"mmm-e"),ARRAYTOTEXT,,0,,(WEEKDAY(a)=6)*(EOMONTH(a,0)-a=30)),,2))Excel solution 10 for Five-Weekend Month Finder, proposed by Oscar Mendez Roca Farell:
=TOCOL(MAP(ROW(2000:2999), LAMBDA(a, LET(r, ROW(1:12)&"/"&a, ARRAYTOTEXT(TEXT(FILTER(r, MMULT(INT((EOMONTH(r, 0)-r+WEEKDAY(r-{1, 7, 6}))/7), {1; 1; 1})=15), "mmm-e"))))), 2)Excel solution 11 for Five-Weekend Month Finder, proposed by Sunny Baggu:
=LET(
_sm, DATE(2000, SEQUENCE(12 * (2999 - 2000 + 1)), 1),
_em, EOMONTH(--_sm, 0),
_d, FILTER(
_sm,
MAP(
_sm,
_em,
LAMBDA(x, y,
LET(
_s, SEQUENCE(y - x + 1, , x),
_wd, WEEKDAY(_s),
_c, AND(BYCOL(N(_wd = {6, 7, 1}), LAMBDA(a, SUM(a))) = 5),
_c
)
)
)
),
_y, YEAR(_d),
MAP(UNIQUE(_y), LAMBDA(e, ARRAYTOTEXT(FILTER(TEXT(_d, "mmm-yyyy"), _y = e))))
)Excel solution 12 for Five-Weekend Month Finder, proposed by Hamidi Hamid:
=LET(
x,
WORKDAY.INTL(
"01/01/2000",
SEQUENCE(
4000
),
"1111000"
),
r,
UNIQUE(
YEAR(
x
)
)*1,
z,
MONTH(
x
)&"-"&YEAR(
x
),
t,
MAP(
z,
LAMBDA(
a,
SUM(
N(
z=a
)
)
)
),
m,
FILTER(
x,
t>14
),
j,
UNIQUE(
TEXT(
m,
"mmm-aaaa"
)
),
n,
UNIQUE(
RIGHT(
j,
4
)
)*1,
dd,
MAP(
r,
LAMBDA(
a,
ARRAYTOTEXT(
FILTER(
j,
TAKE(
RIGHT(
j,
4
)*1,
,
-1
)*1=a,
1/0
)
)
)
),
TOCOL(
dd,
3
)
)Excel solution 13 for Five-Weekend Month Finder, proposed by ferhat CK:
=LET(a,
MAP(SEQUENCE(
1000,
,
2000
),
LAMBDA(y,
LET(tr,
DATE(
y,
1,
1
),
t,
SEQUENCE(
DATE(
y,
12,
31
)-tr+1,
,
tr,
),
q,
WEEKDAY(
t,
1
),
n,
FILTER(t,
(q=1)+(q=6)+(q=7)),
z,
BYROW(SEQUENCE(
12
),
LAMBDA(x,
SUMPRODUCT(--(MONTH(
n
)=x)))),
r,
TEXT(
"1."&FILTER(
SEQUENCE(
12
),
z=15
)&".2000",
"aaa"
)&"-"&YEAR(
tr
),
IFERROR(
TEXTJOIN(
", ",
,
r
),
""
)))),
FILTER(
a,
a<>""
))Excel solution 14 for Five-Weekend Month Finder, proposed by Eddy Wijaya:
=LET(
arr,
EOMONTH(
"1/1/2000",
SEQUENCE(
12*1000,
,
-1
)
)+1,
calc,
DROP(
REDUCE(
0,
arr,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
l_d,
SEQUENCE(
DAY(
EOMONTH(
v,
0
)
),
,
v
),
def,
WEEKDAY(
l_d,
2
),
HSTACK(
SUM(
MAP(
SEQUENCE(
3,
,
5
),
LAMBDA(
m,
ROWS(
FILTER(
def,
def=m
)
)
)
)
)
)
)
)
)
),
1
),
f_arr,
FILTER(
arr,
calc=15
),
DROP(
REDUCE(
0,
YEAR(
f_arr
),
LAMBDA(
a,
v,
UNIQUE(
VSTACK(
a,
TEXTJOIN(
", ",
,
TEXT(
FILTER(
f_arr,
YEAR(
f_arr
)=v,
""
),
"mmm-yyyy"
)
)
)
)
)
),
1
)
)Excel solution 15 for Five-Weekend Month Finder, proposed by El Badlis Mohd Marzudin:
=LET(s,36526,c,SEQUENCE(401768-s+1,,s),t,UNIQUE(TEXT(c,"mmm-e")), f,FILTER(t,MAP(t,--EOMONTH(t+0,0),LAMBDA(s,e, AND(BYCOL(IF(TEXT( SEQUENCE(e-s+1,,s),"ddd")={"Fri","Sat","Sun"},1,0),SUM)=5)))),DROP( GROUPBY(YEAR(f),f,ARRAYTOTEXT,,0),,1))Solving the challenge of Five-Weekend Month Finder with Python in Excel
Python in Excel solution 1 for Five-Weekend Month Finder, proposed by Alejandro Campos:
from datetime import date
def find_months_with_5_weekdays(start_year, end_year):
results = []
for year in range(start_year, end_year + 1):
for month in range(1, 13):
first_day = date(year, month, 1)
weekday_of_first_day = first_day.weekday()
fridays = 0
saturdays = 0
sundays = 0
for day in range(1, 32):
try:
current_date = date(year, month, day)
weekday = current_date.weekday()
if weekday == 4:
fridays += 1
elif weekday == 5:
saturdays += 1
elif weekday == 6:
sundays += 1
except ValueError:
pass
if fridays == 5 and saturdays == 5 and sundays == 5:
results.append([f"{first_day.strftime('%b')}-{year}"])
df = pd.DataFrame(results, columns=['Date'])
df_grouped = df.groupby(df['Date'].str[-4:])['Date'].apply(', '.join)
return df_grouped.reset_index(drop=True)
find_months_with_5_weekdays(2000, 2999)
Python in Excel solution 2 for Five-Weekend Month Finder, proposed by Alejandro Campos:
import calendar
from datetime import date
results = []
for year in range(2000, 3000):
for month in range(1, 13):
first_day = date(year, month, 1)
count_days = {calendar.FRIDAY: 0, calendar.SATURDAY: 0, calendar.SUNDAY: 0}
for day in calendar.monthcalendar(year, month):
for i, cont in count_days.items():
if day[i] != 0:
count_days[i] += 1
if count_days[calendar.FRIDAY] == 5 and count_days[calendar.SATURDAY] == 5 and count_days[calendar.SUNDAY] == 5:
results.append([f"{first_day.strftime('%b')}-{year}"])
df_result = pd.DataFrame(results, columns=['Date'])
df_grouped = df_result.groupby(df_result['Date'].str[-4:])['Date'].apply(', '.join).reset_index(drop=True)
df_grouped
Solving the challenge of Five-Weekend Month Finder with R
R solution 1 for Five-Weekend Month Finder, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = 'Excel/541 Months Having 5 Fri Sat Sun.xlsx'
test = read_xlsx(path)
years = 2000:2999
months = 1:12
dates = expand.grid(year = years, month = months) %>%
mutate(diy = days_in_month(make_date(year, month)),
wday = wday(make_date(year, month, 1), label = TRUE, locale = 'en'),
month_abbr = month(make_date(year, month, 1), label = TRUE, locale = "en"),
date = paste0(month_abbr,"-",year)) %>%
filter(diy == 31, wday == "Fri") %>%
summarise(`Expected Answer` = paste(date, collapse = ", "), .by = year) %>%
arrange(year) %>%
select(-year)
all.equal(test, dates, check.attributes = FALSE)
#> [1] TRUE
&&