List the 1st, 3rd and 5th Saturdays of all months for a given year in A1. Test cases tab has answers for other sample years also for you to test.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 80
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List 1st, 3rd, 5th Saturdays with Power Query
Power Query solution 1 for List 1st, 3rd, 5th Saturdays, proposed by Luan Rodrigues:
let
Fonte = 2022,
a = List.Transform({Number.From(hashtag#date(Fonte,1,1))..Number.From(hashtag#date(Fonte,12,31))},each Date.From(_,"en-us")),
b = Table.FromList(a, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
c = Table.SelectRows(
Table.AddColumn(b, "Personalizar", each Text.Start(Date.DayOfWeekName([Column1],"en-us"),3)),
each [Personalizar] = "Sat"),
d = Table.AddColumn(c, "Personalizar.1", each Date.ToText([Column1],"yyyyMM")),
e = Table.Group(d, {"Personalizar.1"},
{{"Count", each Table.AddIndexColumn(_,"Rank",1,1)}}),
Result = Table.RemoveColumns(
Table.SelectRows(
Table.ExpandTableColumn(e, "Count", {"Column1", "Rank"}, {Text.From(Fonte), "Rank"}),
each ([Rank] <> 2 and [Rank] <> 4)),{"Personalizar.1", "Rank"})
in
Result
Power Query solution 2 for List 1st, 3rd, 5th Saturdays, proposed by Bhavya Gupta:
let
Source = 2021,
FnZ = (a as list, b as date) => List.Contains(a, Number.RoundUp(Date.Day(b)/7)),
FnY = (a as date) => Date.DayOfWeek(a,Day.Saturday),
Criterias = List.Generate(() => [x = hashtag#date(Source,1,1), y = FnY(x), z = FnZ({1,3,5},x)], each [x] < hashtag#date(Source+1,1,1), each [x = Date.AddDays([x], 1), y = FnY(x), z = FnZ({1,3,5},x)]),
FilteredList = List.Transform(List.Select(Criterias, each [y] = 0 and [z]), each [x])
in
FilteredList
Power Query solution 3 for List 1st, 3rd, 5th Saturdays, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Calendar", each {Number.From(hashtag#date([Column1],1,1))..Number.From(hashtag#date([Column1]+1,1,1))}),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Calendar"}),
#"Expanded Calendar" = Table.ExpandListColumn(#"Removed Other Columns", "Calendar"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Calendar",{{"Calendar", type date}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type1",1),
#"Inserted Month Name" = Table.AddColumn(#"Removed Bottom Rows", "Month Name", each Date.MonthName([Calendar]), type text),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day Name", each Date.DayOfWeekName([Calendar]), type text),
#"Filtered Rows" = Table.SelectRows(#"Inserted Day Name", each ([Day Name] = "Saturday")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Month Name"}, {{"All", each _, type table [Calendar=nullable date, Month Name=text, Day Name=text]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"Index",1,1)),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"Calendar", "Index"}, {"Calendar", "Index"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each [Index] = 1 or [Index] = 3 or [Index] = 5),
#"Removed Other Columns2" = Table.SelectColumns(#"Filtered Rows1",{"Calendar"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Other Columns2",{{"Calendar", type date}})
in
#"Changed Type2"
Power Query solution 4 for List 1st, 3rd, 5th Saturdays, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
Power Query solution 5 for List 1st, 3rd, 5th Saturdays, proposed by Matthias Friedmann:
let
Year = Number.From(Excel.CurrentWorkbook(){[Name="Year"]}[Content][Column1]{0}),
Dates = {Number.From(hashtag#date(Year,1,1))..Number.From(hashtag#date(Year,12,31))},
#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), {"Dates"}, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Dates", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each
Date.DayOfWeekName([Dates], "en-US") = "Saturday" and not Number.IsEven(Date.WeekOfMonth([Dates]))
)
in
#"Filtered Rows"
=> Year is a named range cell with any year number.
=> Date.DayOfWeekName([Dates], "en-US") = "Saturday"
Had originally Date.DayOfWeekName([Dates]) = "Samstag"
"Samstag" would only work if you have German setting 😊
Power Query solution 6 for List 1st, 3rd, 5th Saturdays, proposed by Matthias Friedmann:
=> Year is a named range cell with any year number.
=> Date.DayOfWeekName([Dates], "en-US") = "Saturday"Solving the challenge of List 1st, 3rd, 5th Saturdays with Excel
Excel solution 1 for List 1st, 3rd, 5th Saturdays, proposed by Rick Rothstein:
=LET(
d,
DATE(
A1,
ROW(
1:12
),
8
),
f,
d-WEEKDAY(
d-7
),
SORT(
VSTACK(
f,
f+14,
IF(
MONTH(
f
)=MONTH(
f+28
),
f+28,
""
)
)
)
)Excel solution 2 for List 1st, 3rd, 5th Saturdays, proposed by Rick Rothstein:
=LET(
d,
DATE(
A1,
ROW(
1:12
),
8
),
f,
d-WEEKDAY(
d-7
),
SORT(
VSTACK(
f,
f+14,
IF(
DAY(
f+28
)>14,
f+28,
""
)
)
)
)Excel solution 3 for List 1st, 3rd, 5th Saturdays, proposed by محمد حلمي:
=LET(M,
DATE(
A1,
1,
1
),
d,
SEQUENCE(
DATE(
A1,
12,
31
)-M+1,
,
M
),
FILTER(d,
(ISEVEN(
QUOTIENT(
DAY(
d
)-1,
7
)
)*(WEEKDAY(
d
)=7))))Excel solution 4 for List 1st, 3rd, 5th Saturdays, proposed by 🇰🇷 Taeyong Shin:
=LET(
Sat,
WORKDAY.INTL(
DATE(
A1,
SEQUENCE(
12
),
1
) - 1,
{1,
3,
5},
"1111101"
),
UNIQUE(
TOCOL(
IF(
Sat < DATE(
A1 + 1,
1,
1
),
Sat,
NA()
),
2
)
)
)Excel solution 5 for List 1st, 3rd, 5th Saturdays, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_y,
A1,
_s,
DATE(
_y,
1,
1
),
_e,
EDATE(
_s,
12
),
_fs,
_s + 7 - WEEKDAY(
_s - 7
),
_ls,
_e - WEEKDAY(
_e - 7
),
_rw,
(_ls - _fs) / 7 + 1,
_sq,
SEQUENCE(
_rw,
,
_fs,
7
),
_m,
MONTH(
_sq
),
_calc,
MAP(
_m,
SEQUENCE(
_rw
),
LAMBDA(a,
b,
SUM(--(TAKE(
_m,
b
) = a)))
),
_r,
FILTER(
_sq,
ISNUMBER(
XMATCH(
_calc,
{1; 3; 5}
)
)
),
_r
)
Here:
y = Year
s = Start
e = End
fs = First Saturday of the Year
ls = Last Saturday of the Year
rw = Rows (No. of Saturdays)Excel solution 6 for List 1st, 3rd, 5th Saturdays, proposed by Timothée BLIOT:
=LET(Y,A1, D,FILTER(SEQUENCE(366,,DATE(Y,1,1)),YEAR(SEQUENCE(366,, DATE(Y,1,1)))=Y), W,IF(WEEKDAY(D,16)=1,1,0), WD,FILTER(D,W), N,MAP(SEQUENCE(ROWS(WD)),LAMBDA(a,SUMPRODUCT(1*(MONTH(TAKE(WD,a))=INDEX(MONTH(WD),a))))), FILTER(WD,SWITCH(N,1,1,3,1,5,1,0)))Excel solution 7 for List 1st, 3rd, 5th Saturdays, proposed by Bhavya Gupta:
=LET(Year,
A1,
Mx,
DATE(
Year,
12,
31
),
Mn,
DATE(
Year,
1,
1
),
Dates,
SEQUENCE(
Mx-Mn+1,
,
Mn
),
Wd,
WEEKDAY(
Dates
),
FILTER(Dates,
ISNUMBER(
XMATCH(
QUOTIENT(
DAY(
Dates
)-1,
7
)+1,
{1,
3,
5}
)
)*(Wd=7)))Excel solution 8 for List 1st, 3rd, 5th Saturdays, proposed by Owen Price:
= array of integers e.g. {1,
3,
5}
day = integer day of week e.g. Saturday = 7
ofyear = year
*/
NthDaysOfMonthsInYear = LAMBDA(nth,
day,
ofyear,
LET(
d,
DATE(
ofyear,
1,
SEQUENCE(
366
)
),
f,
FILTER(d,
(WEEKDAY(
d
) = day) * (YEAR(
d
) = ofyear)),
q,
ROUNDUP(
DAY(
f
) / 7,
0
),
FILTER(
f,
NOT(
ISERROR(
XMATCH(
q,
nth
)
)
)
)
)
)Excel solution 9 for List 1st, 3rd, 5th Saturdays, proposed by Stefan Olsson:
=LET(
yr,A1,
_fs,DATE(yr,1,8)-WEEKDAY(DATE(yr,1,1)),
_s,SEQUENCE(52+(YEAR(52*7+_fs)=yr)*1,1,_fs,7),
_f,MAP(_s,LAMBDA(i,OR(ArrayFormula(ISBETWEEN(DAY(i)-{28,14,0},1,7,true,true))))),
FILTER(_s,_f)
)Excel solution 10 for List 1st, 3rd, 5th Saturdays, proposed by El Badlis Mohd Marzudin:
=LET(
_dates,
SEQUENCE(
365,
,
"1-1-"&A1
),
_Sat,
FILTER(
_dates,
WEEKDAY(
_dates
)=7
),
_day,
QUOTIENT(
DAY(
_Sat
)-1,
7
)+1,
FILTER(_Sat,
(_day=1)+(_day=3)+(_day=5)))