List the Palindrome dates in 21st century i.e. during years 2000-2099. Dates should be considered in YYYYMMDD format to work out Palindrome dates. Palindrome dates are those dates which are same even if read from backward for example 20700702. Your formula need not be different from others as long as you have worked out your formula independently)
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 68
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Palindromic Dates with Power Query
Power Query solution 1 for Find Palindromic Dates, proposed by Aditya Kumar Darak 🇮🇳:
let
Years = {2000 .. 2099},
Dates = List.Transform(
Years,
each [
T = Text.From(_),
RV = Text.Reverse(T),
J = T & RV,
D = if try Date.From(J) is date otherwise false then J else null
][D]
),
Return = List.RemoveNulls(Dates)
in
ReturnPower Query solution 2 for Find Palindromic Dates, proposed by Luan Rodrigues:
let
Fonte = List.Buffer({Number.From(hashtag#date(2000,01,01))..Number.From(hashtag#date(2099,12,31))}),
dt = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
f = Table.SelectRows(Table.AddColumn(dt, "Personalizar", each
let
Data =
[
a = Date.ToText(Date.From([Column1]),"YYYYMMDD"),
b = Text.Reverse(a),
c = if a =b then true else false
][c]
in
Data), each [Personalizar] = true),
Result = Table.AddColumn(f, "Dates", each Text.Remove(
Date.ToText((Date.From([Column1], "en-US")),"YYYYMMDD"),"/"))[[Dates]]
in
Result
Power Query solution 3 for Find Palindromic Dates, proposed by Antriksh Sharma:
let
Start = hashtag#date(2001, 01, 01),
Stop = hashtag#date(2099, 12, 31),
Count = Number.From ( Stop ) - Number.From ( Start ),
Dates = List.Dates ( Start, Count, hashtag#duration(1, 0, 0, 0) ),
FxPalindromeChecker =
(Date as text)=>
Text.Reverse ( Date ) = Date,
Check =
List.Select (
Dates,
each FxPalindromeChecker (
Date.ToText ( _, "yyyyMMdd" )
)
),
ToTable = Table.FromColumns ( { Check }, type table [Date = date])
in
ToTable
Power Query solution 4 for Find Palindromic Dates, proposed by Victor Wang:
let
Source = List.Transform({1..12}, each let t = Text.PadStart(Text.From(_), 2, "0") in "20" & Text.Reverse(t) & t & "02")
in
Source
His comment:
https://www.linkedin.com/feed/update/urn:li:activity:6999219108592431104?commentUrn=urn%3Ali%3Acomment%3A%28activity%3A6999219108592431104%2C6999246977104236544%29
Power Query solution 5 for Find Palindromic Dates, proposed by Abdoul Karim N.:
let
Start=hashtag#date(2000,01,01),
End= hashtag#date(2099,12,31),
Count=Number.From(End) - Number.From(Start),
Dates= List.Dates(Start, Count, hashtag#duration(1,0,0,0)),
ToTable = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
ID = Table.AddColumn(ToTable, "YYYYMMDD", each Text.Combine(List.Reverse( Text.Split(Text.From([Date]),"/")))),
Reversing = Table.AddColumn(ID, "Reverse", each Text.Reverse([YYYYMMDD])),
Palindrome= Table.AddColumn(Reversing, "Custom", each [YYYYMMDD]=[Reverse]),
Filtering = Table.SelectRows(Palindrome, each ([Custom] = true))[YYYYMMDD]
in
Filtering
Solving the challenge of Find Palindromic Dates with Excel
Excel solution 1 for Find Palindromic Dates, proposed by Rick Rothstein:
=LET(
y,
TEXT(
ROW(
1:99
),
"00"
),
m,
RIGHT(
y
)&LEFT(
y
),
20&FILTER(
y&m,
0+m<13
)&"02"
)Excel solution 2 for Find Palindromic Dates, proposed by Rick Rothstein:
=LET(
d,
TEXT(
SEQUENCE(
36525,
,
DATE(
2000,
1,
1
)
),
"YYYYMMDD"
),
FILTER(
d,
MAP(
d,
LAMBDA(
x,
CONCAT(
MID(
x,
{8,
7,
6,
5,
4,
3,
2,
1},
1
)
)
)
)=d
)
)Excel solution 3 for Find Palindromic Dates, proposed by John V.:
=LET(d,TEXT(36525+ROW(1:36525),"yyymmdd"),FILTER(d,--d=MMULT(--MID(d,COLUMN(A:H),1),10^(ROW(1:8)-1))))Excel solution 4 for Find Palindromic Dates, proposed by محمد حلمي:
=LET(
c,TEXT(SEQUENCE(73050-36526+1,,36526),
"yyyymmdd"),
x,MAP(c,LAMBDA(c,(RIGHT(c,2)="02")*(CONCAT(MID(c,{6,5},1))=
(CONCAT(MID(c,{3,4},1)))))),
FILTER(c,x))Excel solution 5 for Find Palindromic Dates, proposed by 🇰🇷 Taeyong Shin:
=LET(y, SEQUENCE(100, , 2000), num, SEQUENCE(4, , 4, -1),
str, MAP(y, LAMBDA(m, CONCAT(MID(m, num, 1)) )),
FILTER(y&str, ISNUMBER(DATEVALUE(TEXT(y&str, "00-00-00"))))
)
If SEQUENCE(4, , 4, -1)Excel solution 6 for Find Palindromic Dates, proposed by 🇰🇷 Taeyong Shin:
=LET(y,SEQUENCE(100,,2000),TEXT(TOCOL(--TEXT(y&REDUCE("",SEQUENCE(4),LAMBDA(a,i,MID(y,i,1)&a)),"00-00-00"),2),"emmdd"))Excel solution 7 for Find Palindromic Dates, proposed by Kris Jaganah:
=LET(a,TEXT(DATE(1999,12,31)+SEQUENCE(36525,,1,1),"yyyymmdd"),b,FILTER(a,MAP(a,LAMBDA(x,LEFT(x,4)=TEXTJOIN("",TRUE,MID(x,SEQUENCE(,4,8,-1),1))))=TRUE),b)Excel solution 8 for Find Palindromic Dates, proposed by Julian Poeltl:
=LET(S,SEQUENCE(100,,2000),SP,MAP(S,LAMBDA(A,A&CONCAT(MID(A,SEQUENCE(4,,4,-1),1)))),M,MAP(S,LAMBDA(A,CONCAT(MID(A,SEQUENCE(2,,4,-1),1)))),FILTER(SP,NOT(ISERR(MAP(S,M,LAMBDA(A,B,CONCAT("2.",B,".",A)))*1))))Excel solution 9 for Find Palindromic Dates, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_s,
2000,
_e,
2099,
_sd,
DATE(
_s,
1,
1
),
_ed,
DATE(
_e,
12,
31
),
_seq,
SEQUENCE(
_ed - _sd + 1,
,
_sd
),
_txt1,
TEXT(
_seq,
"yyyymmdd"
),
_txt2,
MAP(
_txt1,
LAMBDA(
a,
CONCAT(
MID(
a,
SEQUENCE(
8,
,
8,
-1
),
1
)
)
)
),
_calc,
_txt1 = _txt2,
_r,
FILTER(
_txt1,
_calc
),
_r
)Excel solution 10 for Find Palindromic Dates, proposed by Timothée BLIOT:
=LET(
Dates, TEXT(SEQUENCE(DATE(2099,12,31)-DATE(2000,1,1)+1,,DATE(2000,1,1)),"YYYYMMDD"),
FILTER(Dates, BYROW(Dates, LAMBDA(a, IF( LEFT(a,4)=CONCAT(MID(a,SEQUENCE(4,,8,-1),1)),1,0) ))))Excel solution 11 for Find Palindromic Dates, proposed by Stefan Olsson:
=QUERY(
MAKEARRAY(99,1,
LAMBDA(_r, _c,
IF(0+REGEXREPLACE(TEXT(_r, "00"),"(d)(d)", "$2$1")<13,
TEXT(_r, "2000")®EXREPLACE(TEXT(_r, "00"), "(d)(d)", "$2$102"),"No"
)
)
),
"Where Col1<>'No'", 0)Excel solution 12 for Find Palindromic Dates, proposed by Jardiel Euflázio:
=LET(
b,
SEQUENCE(
36525,
,
36526
),
c,
TEXT(
b,
"yyyymmdd"
),
TEXT(
FILTER(
b,
c=BYROW(
c,
LAMBDA(
a,
CONCAT(
MID(
a,
{87654321},
1
)
)
)
)
),
"yyyymmdd"
)
)Excel solution 13 for Find Palindromic Dates, proposed by El Badlis Mohd Marzudin:
=LET(
sd,
DATE(
2000,
1,
1
),
ed,
DATE(
2099,
12,
31
),
listdate,
TEXT(
SEQUENCE(
ed-sd+1,
,
sd
),
"yyyymmdd"
),
reverse,
BYROW(
listdate,
LAMBDA(
a,
CONCAT(
MID(
a,
SEQUENCE(
MAX(
LEN(
a
)
),
,
MAX(
LEN(
a
)
),
-1
),
1
)
)
)
),
FILTER(
listdate,
listdate=reverse
)
)Excel solution 14 for Find Palindromic Dates, proposed by Ibrahim Sadiq:
=LET(s,DATE(2000,1,1),e,DATE(2099,12,31),ds,e-s+1, x,TEXT(SEQUENCE(ds,,s),"yyyymmdd"),FILTER(x,MAP(x,LAMBDA(y,CONCAT(MID(y,SEQUENCE(,LEN(y),LEN(y),-1),1))))=x))Excel solution 15 for Find Palindromic Dates, proposed by Michael Rogers:
=EXACT(A1,TEXTJOIN("",TRUE,MID(A1,SEQUENCE(LEN(A1),,LEN(A1),-1),1)))Solving the challenge of Find Palindromic Dates with Python in Excel
Python in Excel solution 1 for Find Palindromic Dates, proposed by Alejandro Campos:
def generate_palindrome_dates():
return pd.DataFrame([
{"Date": f"{y:04d}{m:02d}{d:02d}"}
for y in range(2000, 2100)
for m in range(1, 13)
for d in range(1, 32)
if (date_str := f"{y:04d}{m:02d}{d:02d}") == date_str[::-1]])
palindrome_dates_df = generate_palindrome_dates()