Find the counts of weekday names of Christmas during last n years mentioned in A1. I have shown the output for last 100 years. A1 can vary. When I run formula from today to a date till 24-Dec-23, it will count the years from 25-Dec-2022 till 25-Dec-1923. But when I run formula on 25-Dec-23, then it will count the years from 25-Dec-2023 till 25-Dec-1924. Hence, you can’t hard code 25-Dec-22. You will need to derive this date. In essence, I am asking for last 100 occurrences of Christmas. Sort day names of week properly.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 105
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Weekday Count of Christmas with Power Query
Power Query solution 1 for Weekday Count of Christmas, proposed by Bo Rydobon 🇹🇭:
let
t = DateTime.LocalNow(),
x = List.Transform({1..100}, each Date.DayOfWeek(hashtag#date(Date.Year(t) + (if DateTime.ToText(t,"MMdd") >"1224" then 1 else 0)-_,12,25))),
Result = Table.FromRows(List.Transform({1..7}, each {Date.ToText(Date.From(_),"ddd"),List.Count(List.Select(x,(x)=> x=_-1))}),{"DAY","COUNT"} )
in
Result
Power Query solution 3 for Weekday Count of Christmas, proposed by Bo Rydobon 🇹🇭:
let
x = List.Transform({1..100}, each Date.DayOfWeek(hashtag#date(Date.Year(Date.AddDays(DateTime.LocalNow(),7))-_,12,25))),
Result = Table.FromRows(List.Transform({1..7}, each {Date.ToText(Date.From(_),"ddd"),List.Count(List.Select(x,(x)=> x=_-1))}),{"DAY","COUNT"} )
in
Result
=MAKEARRAY(7,2,LAMBDA(w,c,IF(c=1,TEXT(w,"ddd"),SUM(N(w=WEEKDAY("5/"&YEAR(NOW()+7)-SEQUENCE(A1)))))))
Power Query solution 4 for Weekday Count of Christmas, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]{0}[Column1],
DayList = {
Number.From(Date.AddYears(Date.From(DateTimeZone.LocalNow()), - Source)) .. Number.From(
Date.From(DateTimeZone.LocalNow())
)
},
Converted = Table.FromList(DayList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Type = Table.TransformColumnTypes(Converted, {{"Column1", type date}}),
Filtered = Table.SelectRows(Type, each (Date.Month([Column1]) = 12 and Date.Day([Column1]) = 25)),
Tabla = Table.FromColumns(
{
List.Transform(Filtered[Column1], Date.DayOfWeek),
List.Transform(Filtered[Column1], Date.DayOfWeekName)
},
{"Column1", "Day"}
),
Sorted = Table.Sort(Tabla, {{"Column1", Order.Ascending}}),
Solucion = Table.Group(Sorted, {"Day"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
SolucionPower Query solution 5 for Weekday Count of Christmas, proposed by Luan Rodrigues:
let
Fonte = [
a = 100,
b = Date.Year(DateTimeZone.LocalNow()),
c = Date.Year(DateTimeZone.LocalNow()) - a,
d = List.Transform({c .. b - 1}, Text.From),
e = List.Repeat({"12/25"}, a),
f = List.Zip({d, e})
][f],
tab = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null),
a = Table.AddColumn(
tab,
"Personalizar",
each [
A = Text.Combine([Column1], "/"),
DAY = Text.Start(Date.DayOfWeekName(A, "un-EN"), 3),
NDAY = Date.DayOfWeek(Date.From(A))
]
),
b = Table.ExpandRecordColumn(a, "Personalizar", {"DAY", "NDAY"}, {"DAY", "NDAY"}),
c = Table.Group(b, {"NDAY", "DAY"}, {{"COUNT", each Table.RowCount(_), Int64.Type}}),
Result = Table.Sort(c, {{"NDAY", Order.Ascending}})[[DAY], [COUNT]]
in
ResultPower Query solution 6 for Weekday Count of Christmas, proposed by Victor Wang:
let
N = 100,
Transform = let today = Date.From(DateTime.LocalNow()), year = Date.Year(today), xmas = hashtag#date(year,12,25) in List.Transform({0..N-1}, each Date.DayOfWeekName(Date.AddYears(hashtag#date(if today >= xmas and year=Date.Year(xmas) then year else year-1, 12, 25), -_))),
Result = Table.FromRecords(List.Transform({1..7}, each [DAY = Date.DayOfWeekName(_), COUNT = List.Count(List.Select(Transform, (a)=> a = DAY))]))
in
Result
Power Query solution 7 for Weekday Count of Christmas, proposed by Jan Willem Van Holst:
let
numberOfYear = 100, //change number of year here
endDate = hashtag#date(2023,1,1),
startDate = hashtag#date(2023-numberOfYear,1,1),
listOfDays = List.Dates( startDate, Duration.Days(endDate-startDate)+1, hashtag#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(listOfDays, Splitter.SplitByNothing()),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each (Date.Month([Column1]) =12 and Date.Day([Column1])=25)),
#"Extracted Day Name" = Table.TransformColumns(#"Filtered Rows", {{"Column1", each Date.DayOfWeekName(_), type text}}),
#"Grouped Rows" = Table.Group(#"Extracted Day Name", {"Column1"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
Power Query solution 8 for Weekday Count of Christmas, proposed by Ian Segard:
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Last N years", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTime.LocalNow()),
#"Extracted Date" = Table.TransformColumns(#"Added Custom",{{"Custom", DateTime.Date, type date}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Extracted Date", "Custom", "Custom - Copy"),
#"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column",{{"Custom - Copy", Int64.Type}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each [Last N years]*365),
#"Inserted Subtraction" = Table.AddColumn(#"Added Custom1", "Subtraction", each [#"Custom - Copy"] - [Custom.1], type number),
#"Duplicated Column1" = Table.DuplicateColumn(#"Inserted Subtraction", "Subtraction", "Subtraction - Copy"),
Solving the challenge of Weekday Count of Christmas with Excel
Excel solution 1 for Weekday Count of Christmas, proposed by Bo Rydobon 🇹🇭:
=LET(t,
NOW(),
v,
SEQUENCE(
7
),
x,
SORT(MOD("24dec"&YEAR(
t
)-SEQUENCE(
A1
)+(TEXT(
t,
"mdd"
)-1224>0),
7)+1),
HSTACK(
TEXT(
v,
"ddd"
),
XMATCH(
v,
x,
,
-1
)-XMATCH(
v,
x
)+1
))
=HSTACK(TEXT(
SEQUENCE(
7
),
"ddd"
),
MAP(SEQUENCE(
7
),
LAMBDA(w,
SUM(N(w=MOD("24dec"&YEAR(
NOW()
)+(TEXT(
NOW(),
"mdd"
)-1224>0)-SEQUENCE(
A1
),
7)+1)))))Excel solution 2 for Weekday Count of Christmas, proposed by Rick Rothstein:
=HSTACK(TEXT(
ROW(
1:7
),
"ddd"
),
MAP(ROW(
1:7
),
LAMBDA(x,
SUM(0+(WEEKDAY(DATE((TEXT(
NOW(),
"mmdd"
)+0>1224)+YEAR(
NOW()
)-SEQUENCE(
A1
),
12,
25))=x)))))Excel solution 3 for Weekday Count of Christmas, proposed by John V.:
=HSTACK(TEXT(ROW(1:7),"ddd"),FREQUENCY(WEEKDAY(YEAR(NOW())-SEQUENCE(A1)&"-12-25"),ROW(1:6)))Excel solution 4 for Weekday Count of Christmas, proposed by محمد حلمي:
=LET(
e,
DATE(
SEQUENCE(
A1,
,
2023,
-1
),
1,
1
),
v,
TEXT(
e,
"ddd"
),
r,
UNIQUE(
v
),
HSTACK(r,
MAP(r,
LAMBDA(a,
SUM(--(a=v))))))Excel solution 5 for Weekday Count of Christmas, proposed by 🇰🇷 Taeyong Shin:
=LET(
s,
SEQUENCE(
7
),
HSTACK(
TEXT(
s,
"ddd"
),
DROP(
FREQUENCY(
WEEKDAY(
SEQUENCE(
A1,
,
YEAR(
"1-1"
)-A1
)&-12&-25
),
s
),
-1
)
)
)
=LET(
w,
WEEKDAY(
SEQUENCE(
A1,
,
YEAR(
D1
)-A1
)&-12&-25
),
DROP(
GROUPBY(
HSTACK(
w,
TEXT(
w,
"ddd"
)
),
w,
ROWS,
,
0
),
,
1
)
)Excel solution 6 for Weekday Count of Christmas, proposed by Kris Jaganah:
=LET(a,
SEQUENCE(
A1,
,
2022,
-1
),
b,
DATE(
a,
12,
25
),
c,
SEQUENCE(
WEEKDAY(
1,
1
)+6,
,
1
),
d,
BYROW(c,
LAMBDA(x,
SUM(--(WEEKDAY(
b,
1
)=x)))),
HSTACK(
c,
d
))Excel solution 7 for Weekday Count of Christmas, proposed by Kris Jaganah:
=LET(b,
DATE(
SEQUENCE(
A1,
,
YEAR(
NOW()
),
-1
),
12,
25
),
c,
SEQUENCE(
7
),
d,
BYROW(c,
LAMBDA(x,
SUM(--(WEEKDAY(
b,
1
)=x)))),
HSTACK(
TEXT(
c,
"ddd"
),
d
))Excel solution 8 for Weekday Count of Christmas, proposed by Julian Poeltl:
=LET(
D,
WEEKDAY(
DATE(
2023-SEQUENCE(
100
),
12,
25
)
),
MAP(
SEQUENCE(
7
),
LAMBDA(
A,
ROWS(
FILTER(
D,
D=A
)
)
)
)
)Excel solution 9 for Weekday Count of Christmas, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_dt,
TODAY(),
_n,
A1,
_c,
--TEXT(
_dt,
"ddmm"
) < 2512,
_y,
YEAR(
_dt
) - _c,
_dts,
EDATE(
DATE(
_y,
12,
25
),
-SEQUENCE(
_n,
,
0
) * 12
),
_dy,
TEXT(
_dts,
"ddd"
),
_udy,
TEXT(
SEQUENCE(
7
),
"ddd"
),
_cnt,
MAP(_udy,
LAMBDA(a,
SUM(--(_dy = a)))),
_r,
HSTACK(
_udy,
_cnt
),
_r
)Excel solution 10 for Weekday Count of Christmas, proposed by Timothée BLIOT:
=LET(Y,
YEAR(
TODAY()
),
N,
A1,
D,
TEXT(
WEEKDAY(
DATE(
Y-SEQUENCE(
N
),
12,
25
),
1
),
"ddd"
),
S,
BYROW(D,
LAMBDA(x,
SUMPRODUCT(1*(x=D)) )),
UNIQUE(
HSTACK(
D,
S
)
))Excel solution 11 for Weekday Count of Christmas, proposed by Hussein SATOUR:
=LET(
d,
DATE(
SEQUENCE(
A1,
,
YEAR(
TODAY()
) - 1,
-1
),
12,
25
),
dd,
TEXT(
d,
"ddd"
),
c,
MAP(
UNIQUE(
dd
),
LAMBDA(
x,
COUNTA(
FILTER(
dd,
dd = x
)
)
)
),
HSTACK(
UNIQUE(
dd
),
c
)
)Excel solution 12 for Weekday Count of Christmas, proposed by Bhavya Gupta:
=LET(t,
TODAY(),
fd,
IF(
t=DATE(
YEAR(
t
),
12,
25
),
t,
DATE(
YEAR(
t
)-1,
12,
25
)
),
ad,
TEXT(
EDATE(
fd,
-SEQUENCE(
A1,
,
0,
12
)
),
"ddd"
),
day,
TEXT(
SEQUENCE(
7
),
"ddd"
),
HSTACK(day,
MAP(day,
LAMBDA(x,
SUM(--(x=ad))))))Excel solution 13 for Weekday Count of Christmas, proposed by Md. Zohurul Islam:
=LET(
a,
SEQUENCE(
A1,
,
YEAR(
TODAY()
)-1,
-1
),
b,
TEXT(
DATE(
a,
12,
25
),
"ddd"
),
c,
VSTACK(
"Sun",
"Mon",
"Tue",
"Wed",
"Thu",
"Fri",
"Sat"
),
d,
MAP(
c,
LAMBDA(
x,
SUM(
ABS(
b=x
)
)
)
),
hdr,
HSTACK(
"Day",
"Count"
),
rng,
HSTACK(
c,
d
),
e,
VSTACK(
hdr,
rng
),
e
)Excel solution 14 for Weekday Count of Christmas, proposed by Charles Roldan:
=let(
_Days,
sequence(
7
),
_Years,
year(
today()
)-sequence(
A1,
,
0
),
_Xmases,
date(
_Years,
12,
25
),
_Tally,
drop(
frequency(
weekday(
_Xmases
),
_Days
),
-1
),
hstack(
text(
_Days,
"ddd"
),
_Tally
)
)Excel solution 15 for Weekday Count of Christmas, proposed by Diarmuid Early:
=LET(
dayCol,
TEXT(
SEQUENCE(
7
),
"ddd"
),
xmasRow,
TEXT(
DATE(
SEQUENCE(
,
A1,
YEAR(
TODAY()
),
-1
),
12,
25
),
"ddd"
),
dayCounts,
BYROW(--(dayCol=xmasRow),
LAMBDA(
x,
SUM(
x
)
)),
HSTACK(
dayCol,
dayCounts
))
It would be easier if we could use COUNTIFS on a calculated array! : )Excel solution 16 for Weekday Count of Christmas, proposed by Fábio Gatti:
=LAMBDA(pYears,
LET(
vYearToday,
YEAR(
TODAY()
)-IF(
TODAY()<=DATE(
YEAR(
TODAY()
),
12,
25
),
1,
0
),
xDates,
TEXT(
DATE(
SEQUENCE(
pYears,
,
vYearToday,
-1
),
12,
25
),
"ddd"
),
xWeekDays,
PROPER(
TEXT(
SEQUENCE(
7
),
"ddd"
)
),
xCount,
BYROW(xWeekDays,
LAMBDA(vDay,
SUM(--(xDates=vDay)))),
xResult,
HSTACK(
xWeekDays,
xCount
),
xResult
)
)(A1)Excel solution 17 for Weekday Count of Christmas, proposed by roberto mensa:
=FREQUENCY(WEEKDAY(DATE(2023-ROW(1:100),12,25),1),ROW(1:6))Excel solution 18 for Weekday Count of Christmas, proposed by Enrico Giorgi:
=FREQUENCY(
WEEKDAY(
DATEVALUE(
"25/12/"&SEQUENCE(
A1,1,YEAR(
TODAY()
)-1,-1
)
),1
),SEQUENCE(
6,1,1,1
)
)
ITALIAN VERSION
=FREQUENZA(
GIORNO.SETTIMANA(
DATA.VALORE(
"25/12/"&SEQUENZA(
A1;
1;
ANNO(
OGGI()
)-1;
-1
)
);
1
);
SEQUENZA(
6;
1;
1;
1
)
)Excel solution 19 for Weekday Count of Christmas, proposed by Tushar Mehta:
=QUERY(
ARRAYFORMULA(
DATE(
SEQUENCE(
100,
1,
YEAR(
TODAY()
)-1,
-1
),
12,
25
)
),
"select dayofweek(Col1),count(Col1) group by dayofweek(Col1)"
)Solving the challenge of Weekday Count of Christmas with SQL
SQL solution 1 for Weekday Count of Christmas, proposed by Zoran Milokanović:
WITH /* Microsoft SQL Server 2019 */
INPUT
AS
(
SELECT 100 AS A1
),
STARTING_POINT
AS
(
SELECT
CASE
THEN DATEFROMPARTS(YEAR(T.START_DATE) - 1, 12, 25)
ELSE DATEFROMPARTS(YEAR(T.START_DATE), 12, 25)
END AS STARTING_XMAS
FROM
(
SELECT CAST(GETDATE() AS DATE) AS START_DATE
) T
),
DATES
AS
(
SELECT
SP.STARTING_XMAS AS XMAS, 1 AS COUNTER, I.A1
FROM STARTING_POINT SP
CROSS JOIN INPUT I
UNION ALL
SELECT
DATEADD(YEAR, -1, D.XMAS) AS XMAS
,D.COUNTER + 1 AS COUNTER
,D.A1
FROM DATES D
WHERE
D.COUNTER < D.A1
)
SELECT
FORMAT(D.XMAS, 'ddd') AS DAY
,COUNT(*) AS COUNT
FROM DATES D
GROUP BY
FORMAT(D.XMAS, 'ddd')
,DATEPART(DW, D.XMAS)
ORDER BY
DATEPART(DW, D.XMAS)
;
&&