For a date given in cell A1, generate the last Mon through Sun of the month -1 : Last -2 : 2nd Last….-5 : 5th last
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 387
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Last Weekday of Each Month with Power Query
Power Query solution 1 for Last Weekday of Each Month, proposed by Bhavya Gupta:
let
Source = hashtag#date(2024, 3, 15),
Output = Table.FromRecords(List.Transform({-1,-2,-3,-4,-5}, (x)=> Record.FromList(List.Transform({2,3,4,5,6,0,1}, each [d = Date.EndOfWeek(Date.AddDays(Date.AddWeeks(Date.EndOfMonth(Source),x), 1), _), f = if Date.Month(d) = Date.Month(Source) then d else null][f]), {"Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"})))
in
Output
Power Query solution 2 for Last Weekday of Each Month, proposed by Matthias Friedmann:
let
Source = {Number.From(Date.StartOfMonth(Date)) .. Number.From(Date.EndOfMonth(Date))},
Table = Table.FromList(Source, Splitter.SplitByNothing(), {"Dates"}, null, ExtraValues.Error),
Type = Table.TransformColumnTypes(Table, {{"Dates", type date}}),
WeekOfMonth = Table.AddColumn(Type, "Week", each Date.WeekOfMonth([Dates]), Int64.Type),
Subtracted = Table.TransformColumns(
WeekOfMonth,
{{"Week", each _ - List.Max(WeekOfMonth[Week]) - 1, type number}}
),
DayName = Table.AddColumn(
Subtracted,
"Day Name",
each Text.Start(Date.DayOfWeekName([Dates], "en-US"), 3),
type text
),
DayOfWeek = Table.AddColumn(DayName, "Day of Week", each Date.DayOfWeek([Dates]), Int64.Type),
SortedDays = Table.Sort(DayOfWeek, {{"Day of Week", Order.Ascending}}),
Removed = Table.RemoveColumns(SortedDays, {"Day of Week"}),
Pivoted = Table.Pivot(Removed, List.Distinct(Removed[#"Day Name"]), "Day Name", "Dates"),
#"Sorted Week" = Table.Sort(Pivoted, {{"Week", Order.Descending}})
in
#"Sorted Week"Power Query solution 3 for Last Weekday of Each Month, proposed by Luke Jarych:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
StartingDateValue = Table.ColumnNames(Source){0},
Dates = Table.Column(Source, StartingDateValue),
StartingDate = Date.FromText(StartingDateValue),
GetPreviousMonday = (LastDLastDayOfWeekCheck) =>
let
DayOfWeekLastDay = Date.DayOfWeek(LastDLastDayOfWeekCheck),
DaysToSubtract = if DayOfWeekLastDay = Date.DayOfWeek(Date.EndOfWeek(Date.AddDays(Date.AddWeeks(LastDLastDayOfWeekCheck, -1), Day.Monday))) then 6 else 7 - DayOfWeekLastDay,
PreviousMonday = LastDLastDayOfWeekCheck - Duration.From(DaysToSubtract)
in
PreviousMonday,
GetLastDayOfWeek = (StartingDate, NumberOfWeeksToSubtract) =>
let
LastDayOfWeek = Date.EndOfWeek(Date.AddDays(Date.AddWeeks(Date.EndOfMonth(StartingDate), NumberOfWeeksToSubtract), Day.Monday))
in
LastDayOfWeek,
Solving the challenge of Last Weekday of Each Month with Excel
Excel solution 1 for Last Weekday of Each Month, proposed by Bo Rydobon 🇹🇭:
=LET(
a,
EOMONTH(
A1,
0
),
d,
SORT(
SEQUENCE(
5,
7,
a-WEEKDAY(
a,
2
)+7,
-1
),
,
,
1
),
IF(
MONTH(
d
)=MONTH(
a
),
d,
""
)
)Excel solution 2 for Last Weekday of Each Month, proposed by Rick Rothstein:
=LET(
e,
EOMONTH(
A1,
0
),
s,
SEQUENCE(
,
7,
e-WEEKDAY(
e,
2
)+8+7*A2
),
IF(
MONTH(
s
)=MONTH(
A1
),
s,
""
)
)Excel solution 3 for Last Weekday of Each Month, proposed by Rick Rothstein:
=LET(f,
A1-DAY(
A1
)+1,
s,
SEQUENCE(
6,
7,
f-WEEKDAY(
f,
2
)+1
),
c,
IF(
MONTH(
s
)=MONTH(
A1
),
s,
""
),
d,
DROP(c,
-(@TAKE(
c,
-1
)="")),
CHOOSEROWS(
d,
ROWS(
d
)+1+A2
))Excel solution 4 for Last Weekday of Each Month, proposed by John V.:
=LET(
d,
EOMONTH(
A1,
0
),
b,
d-WEEKDAY(
d,
2
)-7*ROW(
1:6
)+COLUMN(
H:N
),
IF(
MONTH(
b
)=MONTH(
A1
),
b,
""
)
)Excel solution 5 for Last Weekday of Each Month, proposed by محمد حلمي:
=LET(
i,
WORKDAY.INTL(
EOMONTH(
A1,
0
),
+A2:A6,
"0111111"
)+SEQUENCE(
,
7
)-1,
IF(
MONTH(
i
)=MONTH(
A1
),
i,
""
)
)Excel solution 6 for Last Weekday of Each Month, proposed by محمد حلمي:
=LET(
i,
WORKDAY.INTL(
EOMONTH(
A1,
0
)+1,
+A2:A6,
SUBSTITUTE(
1111111,
1,
0,
SEQUENCE(
,
7
)
)
),
IF(
MONTH(
i
)=MONTH(
A1
),
i,
""
)
)Excel solution 7 for Last Weekday of Each Month, proposed by 🇰🇷 Taeyong Shin:
=LET(
dt,
WORKDAY.INTL(
EOMONTH(
A1,
0
)+1,
+A2:A6,
SUBSTITUTE(
1111111,
1,
0,
SEQUENCE(
,
7
)
)
),
IF(
EOMONTH(
A1,
-1
)Excel solution 8 for Last Weekday of Each Month, proposed by Kris Jaganah:
=LET(
a,
EOMONTH(
A1,
{-1;0}
),
b,
DROP(
a,
1
),
c,
SUM(
-@a,
b
),
d,
SEQUENCE(
c
),
e,
@a+d,
f,
SORTBY(
e,
WEEKDAY(
e,
2
),
1,
e,
-1
),
g,
WEEKDAY(
f,
2
),
h,
XMATCH(
g,
g
)-d-1,
PIVOTBY(
h,
g,
f,
SUM,
1,
0,
-1,
0
)
)Excel solution 9 for Last Weekday of Each Month, proposed by Julian Poeltl:
=LET(
Date,
A1,
EoM,
EOMONTH(
Date,
0
),
SEQAM,
SEQUENCE(
DAY(
EoM
),
,
EoM,
-1
),
SEQFM,
IFERROR(
SEQUENCE(
7-WEEKDAY(
EoM,
2
),
,
7-WEEKDAY(
EoM,
2
)+EoM,
-1
)*NV,
""
),
SEQmF,
VSTACK(
IF(
WEEKDAY(
EoM,
1
)<>1,
SEQFM,
0
),
SEQAM
),
SEQ,
FILTER(
SEQmF,
SEQmF<>0
),
ARR,
L_ReverseHorizontalArray(
WRAPROWS(
SEQ,
7,
""
)
),
ARR
)
Description of my approach:
SEQAM = Sequence from the end of the month to the beginning of the month
SEQFM = Sequence from the first Sunday of the following month to the first day of the following month - Values: "" (by using IFERROR and a provoked error)
SEQmF = Vertical stack from SEQFM and SEQAM - if the last day of the month = Sunday,
SEQFM is replaced by a zero
SEQ = Filtering out the zero
ARR = Row break every 7 columns - this is easily possible because the days up to the first Sunday of the following month are filled with "" - however,
the array is still the wrong way up horizontally,
which is why it is also rotated horizontally - L_ReverseHorizontalArray is one of my pre-programmed Lambdas:
=LAMBDA(
Array,
TRANSPOSE(
INDEX(
TRANSPOSE (
Array
),
SEQUENCE(
ROWS(
MTRANS(
Array
)
),
1,
ROWS(
TRANSPOSE (
Array
)
),
-1
),
SEQUENCE(
1,
COLUMNS(
TRANSPOSE (
Array
)
)
)
)
)
)Excel solution 10 for Last Weekday of Each Month, proposed by Timothée BLIOT:
=DROP(
IFNA(
LET(
B,
DATE(
YEAR(
A1
),
MONTH(
A1
),
1
),
E,
EOMONTH(
A1,
0
),
D,
DAY(
E
),
S,
SORT(
SEQUENCE(
E-B+1,
,
B
),
,
-1
),
W,
WEEKDAY(
S,
2
),
REDUCE(
"",
SEQUENCE(
7
),
LAMBDA(
a,
v,
HSTACK(
a,
VSTACK(
TEXT(
v+1,
"DDD"
),
FILTER(
S,
W=v
)
)
)
)
)
),
""
),
,
1
)Excel solution 11 for Last Weekday of Each Month, proposed by Hussein SATOUR:
=LET(
a,
EOMONTH(
A1,
0
),
b,
EOMONTH(
A1,
-1
),
c,
VSTACK(
IF(
ISNUMBER(
SEQUENCE(
35-a+b
)
),
""
),
SEQUENCE(
a-b,
,
b+1
)
),
SORTBY(
WRAPROWS(
c,
7
),
6-SEQUENCE(
5
)
)
)Excel solution 12 for Last Weekday of Each Month, proposed by LEONARD OCHEA 🇷🇴:
=LET(
f,
EOMONTH(
A1,
0
),
d,
f-WEEKDAY(
f,
2
)+SEQUENCE(
,
7
)-7*SEQUENCE(
6
)+7,
IF(
MONTH(
A1
)=MONTH(
d
),
d,
""
)
)Excel solution 13 for Last Weekday of Each Month, proposed by LEONARD OCHEA 🇷🇴:
SEQUENCE(
6,
7,
a-WEEKDAY(
a,
2
)+7,
-1
)Excel solution 14 for Last Weekday of Each Month, proposed by Mihai Radu O:
=LET(
a,
A1,
eom,
EOMONTH(
a,
0
),
z,
SORT(
SEQUENCE(
DAY(
eom
),
,
EOMONTH(
a,
-1
) + 1
),
,
-1
),
t,
PIVOTBY(
WEEKNUM(
z,
2
)-WEEKNUM(
eom,
2
)-1,
WEEKDAY(
z,
2
),
z,
SUM,
0,
0,
-1,
0
),
hd,
HSTACK(
" ",
B1:H1
),
VSTACK(
hd,
DROP(
t,
1
)
)
)Excel solution 15 for Last Weekday of Each Month, proposed by Giorgi Goderdzishvili:
=LET(
_wk,
TEXT(
DATE(
2024,
1,
SEQUENCE(
7
)
),
"ddd"
),
_lst,
BYCOL(1*(_wk<>$B$1:$H$1),
CONCAT),
_fn,
MAKEARRAY(
5,
7,
LAMBDA(
r,
c,
LET(
o,
WORKDAY.INTL(
EOMONTH(
A1,
0
)+1,
INDEX(
A2:A6,
r,
1
),
INDEX(
_lst,
1,
c
)
),
IF(
MONTH(
o
)=MONTH(
A1
),
o,
""
)
)
)
),
_fn)Excel solution 16 for Last Weekday of Each Month, proposed by Diarmuid Early:
=LET(
dt,
A1,
hdrs,
B1:H1,
eom,
EOMONTH(
dt,
0
),
sq,
SEQUENCE(
DAY(
eom
),
,
eom,
-1
),
wrap,
IFNA(
WRAPROWS(
sq,
7
),
“”
),
sort,
SORTBY(
wrap,
XMATCH(
TEXT(
TAKE(
wrap,
1
),
“ddd”
),
hdrs
)
),
sort
)
* sq is the sequence of dates in reverse order
* wrap wraps that in rows of 7,
so each column is one day in reverse order (I think there’s a way to IFNA within WRAPROWS,
but not confident of the syntax!)Excel solution 17 for Last Weekday of Each Month, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(
fm,
EOMONTH(
A1,
0
),
fe,
SEQUENCE(
DAY(
fm
),
,
fm,
-1
),
ca,
FILTER(
fe,
WEEKDAY(
fe
)=1
)-SEQUENCE&(
,
7,
6,
-1
),
IF(
MONTH(
A1
)=MONTH(
ca
),
ca,
""
)
)Excel solution 18 for Last Weekday of Each Month, proposed by Surendra Reddy:
=LET(a,A1,b,A2:A6,x,WORKDAY.INTL(EOMONTH(a,0),1,"0111111")+(7*b),y,HSTACK(x,x+SEQUENCE(,6)),IF(MONTH(y)<>MONTH(a),"",y))Excel solution 19 for Last Weekday of Each Month, proposed by Surendra Reddy:
=LET(a,
A1,
b,
A2:A6,
x,
WORKDAY.INTL(
EOMONTH(
a,
0
),
1,
"0111111"
)+(7*b),
y,
HSTACK(
x,
x+SEQUENCE(
,
6
)
),
VSTACK(
TEXT(
TAKE(
y,
1
),
"ddd"
),
IF(
MONTH(
y
)<>MONTH(
a
),
"",
y
)
))Solving the challenge of Last Weekday of Each Month with Python
Python solution 1 for Last Weekday of Each Month, proposed by Luke Jarych:
import pandas as pd
import xlwings as xw
import datetime as dt
import calendar as ca
sh = wb.sheets[0]
table = sh.tables['Table1']
rng = sh.range(table.range.address)
df = rng.options(pd.DataFrame, header = True, index=False, numbers=int).value
date = pd.to_datetime(df.columns[0], dayfirst=True).date()
week_names = [(dt.datetime(2024, 1, 1) + dt.timedelta(days=i)).strftime('%a') for i in range(7)]
number_of_days = ca.monthrange(date.year, date.month)[1]
first_day_weekday = ca.monthrange(date.year, date.month)[0]
first_day_of_month = date.replace(day=1)
month_dates = [first_day_of_month + dt.timedelta(days=i) for i in range(number_of_days)]
list_with_nulls = [None] * first_day_weekday
whole_list = list_with_nulls + month_dates
chunks = []
for i in range(0, len(whole_list), 7):
chunk = whole_list[i:i + 7]
chunks.append(chunk)
reversed_chunks = chunks[::-1]
df = pd.DataFrame(reversed_chunks, columns=week_names)
Solving the challenge of Last Weekday of Each Month with R
R solution 1 for Last Weekday of Each Month, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
mutate(across(everything(), as.Date))
pull()
df = data.frame(date = seq(floor_date(date, "month"),
ceiling_date(date, "month") - days(1),
by = "day") %>%
as.Date()) %>%
mutate(week = week(date),
wday = wday(date, label = T, abbr = T, week_start = 1, locale = "US_us")) %>%
pivot_wider(names_from = wday, values_from = date) %>%
select(week, Mon, Tue, Wed, Thu, Fri, Sat, Sun) %>%
arrange(desc(week)) %>%
select(-week)
&&