Find the next 3 Friday the 13th dates for dates given in column A
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 310
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find the next 3 Friday the 13th dates with Power Query
Power Query solution 1 for Find the next 3 Friday the 13th dates, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
F = each Date.DayOfWeek(_, 1) = 4 and Date.Day(_) = 13,
A = each Date.AddDays(_, 1),
S = Table.FromRows(
Table.TransformRows(
Source,
each List.RemoveNulls(
List.Generate(
() => [d = A(Date.FromText([Date])), n = 0],
each [n] < 3,
each [d = A([d]), n = [n] + Number.From(F([d]))],
each if F([d]) then [d] else null
)
)
)
)
in
SPower Query solution 2 for Find the next 3 Friday the 13th dates, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Dates = Table.AddColumn(
Source,
"A",
each
let
n = Table.FromRecords(
let
a = Date.From([Date]),
b = List.Generate(
() => [r = a, x = 0],
each [x] <= 3,
each [
r = Date.AddDays([r], 1),
x =
if Date.DayOfWeek(Date.AddDays([r], 1))
= 5 and Date.Day(Date.AddDays([r], 1))
= 13
then
[x] + 1
else
[x]
]
)
in
b
),
m = Table.FromRows(
{Table.SelectRows(Table.Group(n, {"x"}, {"B", each List.First([r])}), each [x] <> 0)[B]}
)
in
m
),
Sol = Table.ExpandTableColumn(Dates, "A", Table.ColumnNames(Dates[A]{0}))
in
SolPower Query solution 3 for Find the next 3 Friday the 13th dates, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
data = Table.AddColumn(
Fonte,
"data",
each [
a = {
Number.From(Date.From([Date])) .. List.Max(
List.Transform(Fonte[Date], each Number.From(Date.From(_)))
)
* 365
},
b = List.FirstN(
List.Select(a, (x) => Date.Day(Date.From(x)) = 13 and Date.DayOfWeek(Date.From(x)) = 5),
3
),
c = Table.FromRows({List.Transform(b, each Date.From(_, "en-US"))})
][c]
),
res = Table.ExpandTableColumn(data, "data", Table.ColumnNames(data[data]{0}))
in
resPower Query solution 4 for Find the next 3 Friday the 13th dates, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ReType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
First3Fri13s = Table.AddColumn(ReType, "Friday13", each [
a = [Date],
c = List.Dates(a, 1000, hashtag#duration(1,0,0,0)),
d = List.Select( c, each Date.Day( _ ) = 13),
e = List.Select(d, each Date.DayOfWeekName(_) = "Friday"),
f = List.FirstN( e, 3)
][f]),
Extract = Table.TransformColumns(First3Fri13s, {"Friday13", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
Split = Table.SplitColumn(Extract, "Friday13", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Friday13_1", "Friday13_2", "Friday13_3"}, Date.Type),
Retype2 = Table.TransformColumnTypes(Split,{{"Friday13_1", type date}, {"Friday13_2", type date}, {"Friday13_3", type date}})
in
Retype2
Power Query solution 5 for Find the next 3 Friday the 13th dates, proposed by Luke Jarych:
let
Source = Table1,
ColumnNames = {"1_NextFriday13", "2_NextFriday13", "3_NextFriday13"},
AddTableColumn = Table.AddColumn(Source, "Next Fridays", each
let
a = [Date],
countFridays = 3,
b = List.Dates([Date], countFridays * 365, hashtag#duration(1,0,0,0)),
c = List.Select(b, each Date.Day(_) = 13 and Date.DayOfWeekName(_, "en-US") = "Friday"),
d = Record.FromList(List.FirstN(c, 3), ColumnNames)
in d ),
ExpandTable = Table.ExpandRecordColumn(AddTableColumn, "Next Fridays", ColumnNames)
in
ExpandTable
Solving the challenge of Find the next 3 Friday the 13th dates with Excel
Excel solution 1 for Find the next 3 Friday the 13th dates, proposed by Bo Rydobon 🇹🇭:
=DROP(REDUCE(0,A2:A10,LAMBDA(a,d,LET(f,EOMONTH(d,SEQUENCE(,30,-1))+13,TAKE(VSTACK(a,FILTER(f,MOD(f,7)=6)),,3)))),1)Excel solution 2 for Find the next 3 Friday the 13th dates, proposed by Rick Rothstein:
=DROP(0+TEXTSPLIT(REDUCE("",
A2:A10,
LAMBDA(a,
x,
LET(d,
x+SEQUENCE(
,
9999
),
a&"*"&TEXTJOIN("|",
,
TAKE(TOROW(IF((DAY(
d
)=13)*(WEEKDAY(
d
)=6),
d,
1/0),
3),
,
3))))),
"|",
"*"),
1)Excel solution 3 for Find the next 3 Friday the 13th dates, proposed by John V.:
=TAKE(
REDUCE(
0,
A2:A10,
LAMBDA(
a,
v,
LET(
d,
v+SEQUENCE(
,
999
),
VSTACK(
a,
FILTER(
TEXT(
d,
"e-mm-d"
),
TEXT(
d,
"ddd d"
)="fri 13"
)
)
)
)
),
-9,
3
)Excel solution 4 for Find the next 3 Friday the 13th dates, proposed by محمد حلمي:
=MAKEARRAY(9,
3,
LAMBDA(r,
c,
LET(d,
ROW(
1:900
)+INDEX(
A2:A10,
r
),
INDEX(FILTER(d,
(DAY(
d
)=13)*(WEEKDAY(
d
)=6)),
c))))Excel solution 5 for Find the next 3 Friday the 13th dates, proposed by Kris Jaganah:
=DROP(REDUCE("",A2:A10,LAMBDA(x,y,VSTACK(x,LET(a,DATE(YEAR(y),MONTH(y)+SEQUENCE(,50,0),13),TEXT(TAKE(FILTER(a,WEEKDAY(a)=6),,3),"yyyy-mm-dd"))))),1)Excel solution 6 for Find the next 3 Friday the 13th dates, proposed by Kris Jaganah:
=DROP(REDUCE("",A2:A10,LAMBDA(x,y,VSTACK(x,LET(a,y+SEQUENCE(,1000),TEXT(TAKE(FILTER(a,(WEEKDAY(a)=6)*(DAY(a)=13)),,3),"yyyy-mm-dd"))))),1)Excel solution 7 for Find the next 3 Friday the 13th dates, proposed by Timothée BLIOT:
=DROP(REDUCE("",
A2:A10,
LAMBDA(w,
z,
LET(A,
SEQUENCE(
,
9999,
z
),
b,
MAP(A,
LAMBDA(x,
(DAY(
x
)=13)*(WEEKDAY(
x
)=6))),
VSTACK(
w,
TEXT(
TAKE(
FILTER(
A,
b
),
,
3
),
"YYYY-MM-DD"
)
)))),
1)Excel solution 8 for Find the next 3 Friday the 13th dates, proposed by Hussein SATOUR:
=LET(a, SEQUENCE(300000), b, FILTER(a, (MOD(a, 7) = 6) * (DAY(a) = 13)), c, TEXTSPLIT(CONCAT(MAP(--A2:A10, LAMBDA(x, CONCAT(TAKE(FILTER(b, b>x), 3)&"/")&"|"))), "/", "|",1), TEXT(c, "e-mm-dd"))Excel solution 9 for Find the next 3 Friday the 13th dates, proposed by Sunny Baggu:
=DROP(
REDUCE(
"Happy Dussehra/ Vijayadashami Wishes Vijay Sir 🌼",
A2:A10,
LAMBDA(a, v,
VSTACK(
a,
LET(
_list, v + SEQUENCE(999),
_day, TEXT(_list, "ddd"),
_date, TEXT(_list, "dd"),
TOROW(
TAKE(
TEXT(
FILTER(_list, (_day = "Fri") * (_date = "13")),
"yyyy-mm-dd"
),
3
)
)
)
)
)
),
1
)Excel solution 10 for Find the next 3 Friday the 13th dates, proposed by Abdallah Ally:
=DROP(REDUCE("",
A2:A10,
LAMBDA(x,
y,
VSTACK(x,
LET(rfn,
LAMBDA(f,
v,
a,
IF(COUNTA(
a
)=4,
a,
f(f,
IF((WEEKDAY(
v+1,
16
)=7)*(DAY(
v+1
)=13),
v+2,
v+1),
IF((WEEKDAY(
v+1,
16
)=7)*(DAY(
v+1
)=13),
EXPAND(
a,
COUNTA(
a
)+1,
,
v+1
),
a)))),
DROP(
TOROW(
rfn(
rfn,
y,
""
)
),
,
1
))))),
1)Excel solution 11 for Find the next 3 Friday the 13th dates, proposed by Abdallah Ally:
=DROP(REDUCE("",
A2:A10,
LAMBDA(u,
v,
VSTACK(u,
LET(a,
v,
b,
REDUCE("",
IFERROR(
SEQUENCE(
OFFSET(
a,
1,
0
)-a-1,
,
a+1
),
SEQUENCE(
5000,
,
a+1
)
),
LAMBDA(x,
y,
IF((COUNTA(
x
)<4)*(WEEKDAY(
y,
16
)=7)*(DAY(
y
)=13),
EXPAND(
x,
,
COUNTA(
x
)+1,
y
),
x))),
DROP(
TOROW(
b
),
,
1
))))),
1)Excel solution 12 for Find the next 3 Friday the 13th dates, proposed by 🇵🇪 Ned Navarrete C.:
= TEXT(TEXTSPLIT(TEXTJOIN("/",
,
MAP(A2:A10,
LAMBDA(r,
LET(f,
SEQUENCE(
10^4,
,
r
),
p,
( WEEKDAY(
f
)=6)*(DAY(
f
)=13),
TEXTJOIN(
"*",
,
TOROW(
TAKE(
FILTER(
f,
p
),
3
)
)
))))),
"*",
"/"),
"yyyy-mm-dd")Excel solution 13 for Find the next 3 Friday the 13th dates, proposed by Charles Roldan:
=LET(_isFriday13th,
LAMBDA(
x,
AND(
WEEKDAY(
x
) = 6,
DAY(
x
) = 13
)
),
_FirstN,
LAMBDA(f,
n,
y,
SCAN(y,
SEQUENCE(
n
),
LAMBDA(a,
b,
LAMBDA(
g,
g(
g
)
)(LAMBDA(
g,
LAMBDA(
k,
IF(
f(
k + 1
),
k + 1,
g(
g
)(
k + 1
)
)
)
))(a)))),
WRAPROWS(
DROP(
REDUCE(
"",
A2:A10,
LAMBDA(
a,
b,
VSTACK(
a,
_FirstN(
_isFriday13th,
3,
b
)
)
)
),
1
),
3
))Excel solution 14 for Find the next 3 Friday the 13th dates, proposed by Julien Lacaze:
=TEXT(DROP(REDUCE("",
A2:A10,
LAMBDA(a,
v,
LET(d,
SEQUENCE(
2000,
,
v
),
VSTACK(a,
TOROW(TAKE(FILTER(d,
(DAY(
d
)=13)*(WEEKDAY(
d,
15
)=1)),
3)))))),
1),
"yyyy-mm-dd")Excel solution 15 for Find the next 3 Friday the 13th dates, proposed by Oscar Javier Rosero Jiménez:
=DROP(
REDUCE(
"",
A2:A10,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
SCAN(
EOMONTH(
y,
-2
),
SEQUENCE(
100,
,
1,
0
),
LAMBDA(
a,
b,
EOMONTH(
a,
b
)
)
),
_a,
a + 13,
_f,
FILTER(
_a,
WEEKDAY(
_a
) = 6
),
TOROW(
TAKE(
_f,
3
)
)
)
)
)
),
1
)Excel solution 16 for Find the next 3 Friday the 13th dates, proposed by Pieter de Bruijn:
=LET(d,
EOMONTH(
+A2:A10,
SEQUENCE(
,
140
)
)+13,
f,
MOD(
d,
7
)=6,
x,
TOCOL(
d/f,
2
),
y,
TOCOL(
ROW(
A2:A10
)/f,
2
),
WRAPROWS(FILTER(x,
MMULT((TOROW(
y
)=y)*(TOROW(
x
)<=x),
y^0)<4),
3))
or lambda:
=DROP(
REDUCE(
0,
A2:A10,
LAMBDA(
a,
b,
LET(
d,
SEQUENCE(
,
140,
b+13-MOD(
b,
7
),
7
),
VSTACK(
a,
TAKE&(
FILTER(
d,
DAY(
d
)=13
),
,
3
)
)
)
)
),
1
)Excel solution 17 for Find the next 3 Friday the 13th dates, proposed by Nicolas Micot:
=LET(
_dates;
DATE(
ANNEE(
A2
);
MOIS(
A2
)+SEQUENCE(
100;
;
0
)+SI(
JOUR(
A2
)>13;
1;
0
);
13
);
_vendredi;
FILTRE(
_dates;
JOURSEM(
_dates;
11
)=5;
""
);
TRANSPOSE(
PRENDRE(
_vendredi;
3
)
)
)Excel solution 18 for Find the next 3 Friday the 13th dates, proposed by Ziad A.:
=DROP(
REDUCE(
0,
A2:A10,
LAMBDA(
a,
c,
VSTACK(
a,
LET(
d,
c+ROW(
1:999
),
TOROW(
TAKE(
FILTER(
d,
TEXT(
d,
"ddd d"
)="Fri 13"
),
3
)
)
)
)
)
),
1
)Excel solution 19 for Find the next 3 Friday the 13th dates, proposed by Giorgi Goderdzishvili:
=LET(
arr,
MAP(A2:A10,
LAMBDA(x,
LET(
td,
x,
frd,
WORKDAY.INTL(
td,
SEQUENCE(
,
150
),
"1111011"
),
ds,
DAY(
frd
),
flt,
FILTER(
frd,
ds=13
),
fin,
TEXTJOIN(" ",
,
(TAKE(
flt,
,
3
))),
fin))),
1*TEXTSPLIT(
TEXTJOIN(
",",
,
arr
),
" ",
","
))Excel solution 20 for Find the next 3 Friday the 13th dates, proposed by Abdelrahman Omer, MBA, PMP:
=DROP(REDUCE("",MAP(A2:A10,LAMBDA(a,LET(b,a+6-WEEKDAY(a),ARRAYTOTEXT(TEXT(SMALL(FILTER((SEQUENCE(365*3/7,,b,7)),DAY(SEQUENCE(365*3/7,,b,7))=13),SEQUENCE(3)),"YYYY-MM-DD"))))),LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,",")))),1)Excel solution 21 for Find the next 3 Friday the 13th dates, proposed by Daniel Garzia:
=DROP(REDUCE(0,
A2:A10,
LAMBDA(a,
x,
LET(d,
SEQUENCE(
,
999,
x
),
VSTACK(a,
TAKE(TOROW(d/(TEXT(
d,
"ddd.dd"
)="fri.13"),
2),
,
3))))),
1)Excel solution 22 for Find the next 3 Friday the 13th dates, proposed by Hazem Hassan:
=13)*(TEXT(
a,
"ddd"
)="Fri")=1),
3)&"*"))))&"-"),
"*",
"-",
1)Excel solution 23 for Find the next 3 Friday the 13th dates, proposed by Juliano Santos Lima:
=LET(dts;
SEQUENCE(
365*3;
;
YourDate
);
TRANSPOSE(SMALL(FILTER(dts;
(WEEKDAY(
dts
)=6)*(DAY(
dts
)=13));
{1;
2;
3})))Excel solution 24 for Find the next 3 Friday the 13th dates, proposed by Jeff Blakley:
=DROP(REDUCE("",
A2:A10,
LAMBDA(a,
v,
LET(d,
EDATE(v-DAY(
v
)+1,
SEQUENCE(,
48,
--(DAY(
v
)>1))),
r,
TAKE(
FILTER(
d,
WEEKDAY(
d
)=1
),
,
3
)+12,
VSTACK(
a,
r
)))),
1)Solving the challenge of Find the next 3 Friday the 13th dates with Python in Excel
Python in Excel solution 1 for Find the next 3 Friday the 13th dates, proposed by John V.:
Hi everyone!
One (Python) option could be:
def i(d):
return d.replace(year = d.year + (d.month == 12), month = 1 + d.month % 12)
def a(b):
b, c, r = date.fromisoformat(b), 0, []
d = b + timedelta(13 - b.day)
if d > b:
d = i(d)
while c < 3:
if d.weekday() == 4:
r.append(d.strftime('%Y-%m-%d'))
c += 1
d = i(d)
return r
[a(i) for i in xl("A2:A10")[0]]
Blessings!
Solving the challenge of Find the next 3 Friday the 13th dates with R
R solution 1 for Find the next 3 Friday the 13th dates, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(lubridate)
library(readxl)
library(data.table)
col_names = c("friday_13th_1", "friday_13th_2", "friday_13th_3"))
get_3_f13<- function(date) {
start_date <- as.Date(paste0(year(date), "-", month(date), "-13"))
end_date <- start_date + years(3)
date_seq_13th <- seq.Date(from=start_date, to=end_date, by="month")
friday_13ths <- date_seq_13th[wday(date_seq_13th) == 6 & date_seq_13th > date]
head(friday_13ths, 3)
}
next_3_tv = input %>%
rowwise() %>%
mutate(friday_13th_1 = get_3_f13(Date)[1] %>% as.character(),
friday_13th_2 = get_3_f13(Date)[2] %>% as.character() ,
friday_13th_3 = get_3_f13(Date)[3] %>% as.character()) %>%
ungroup() %>%
select(-Date)
identical(test, next_3_tv)
#> [1] TRUE
&&