Dear Patrons, =LET(n,”72658080893278698732896965823250485053″,CONCAT(CHAR(MID(n,SEQUENCE(LEN(n)/2,,,2),2)))) Find out which dates are Palindromic dates in year 2025. The formats can be MDY, DMY, YMD. List the corresponding format also. Ex. 5-Feb-2025 – In DMY format, this is 5225 which is a Palindromic number. 2-May-2025 – In MDY format this is 5225 and in YMD format, this is 2552. Hence, this date is palindromic in both MDY and YMD format.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 621
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Identify 2025 Palindromic Dates with Power Query
Power Query solution 1 for Identify 2025 Palindromic Dates, proposed by Kris Jaganah:
let
A = List.Dates( hashtag#date(2025,1,1) , 365, hashtag#duration(1,0,0,0)),
B = List.Transform( A , each let a = (x)=>
[p = Date.ToText(_ ,[Format = x]) ,q = if Text.Reverse(p)=p then Text.Upper( x) else null][q] ,
b = {_, Text.Combine( {a("dMy"),a("Mdy"),a("yMd")},", " ) } in b ),
C = Table.FromRows( List.Select( B , each _{1} <> ""),{"Date" ,"Format"} )
in
C
Power Query solution 2 for Identify 2025 Palindromic Dates, proposed by Luan Rodrigues:
let
Fonte = let
a = List.Dates(hashtag#date(2025,1,1),365,hashtag#duration(1,0,0,0) ),
b = List.Transform(a, each List.Transform({"dMy","Mdy","yMd"},(x)=> [ d = Date.ToText(_,x), r = Text.Reverse(d), c = {d,r,Date.ToText(_),x} ][c])),
c = Table.FromRows(List.Transform(List.Select(List.Combine(b),(y)=> y{0} = y{1}),(x)=> List.LastN(x,2)),{"Date","Format"})
in c,
grp = Table.Group(Fonte, {"Date"}, {"Format", each Text.Upper(Text.Combine(_[Format],", ")) } )
in
grp
Power Query solution 3 for Identify 2025 Palindromic Dates, proposed by Abdallah Ally:
let
Year = 2025, // Adjust the year accordingly
GetDateFormats = (dt as date) as list =>
[
a = Date.ToText,
b = {{a(dt, "Mdy"), "MDY"}, {a(dt, "dMy"), "DMY"}, {a(dt, "yMd"), "YMD"}},
c = List.Transform(b, each if _{0} = Text.Reverse(_{0}) then _{1} else null),
d = {dt, Text.Combine(List.RemoveNulls(c), ", ")}
][d],
Dates = List.Dates(hashtag#date(Year, 1, 1), Date.DayOfYear(hashtag#date(Year, 12, 31)), Duration.From(1)),
FromList = Table.FromList(Dates, each GetDateFormats(_), {"Date", "Format"}),
Select = Table.SelectRows(FromList, each [Format] <> ""),
Result = Table.TransformColumnTypes(Select, {{"Date", Date.Type}, {"Format", Text.Type}})
in
Result
Power Query solution 4 for Identify 2025 Palindromic Dates, proposed by Mihai Radu O:
let
an = 2025,
fPal = (x)=> Number.From(Text.Reverse(Text.From(x)))= x,
format = {"dMy","Mdy","yMd"},
zile = List.Dates(hashtag#date(an,1,1),Duration.Days(hashtag#date(an,12,31)-hashtag#date(an,1,1))+1,hashtag#duration(1,0,0,0)),
a = List.Transform(zile, (z)=> Text.Upper(Text.Combine( List.Transform(format,(x)=> if fPal( Number.From(Date.ToText(z,x))) then x else null),", "))),
b = Table.FromRows( List.Select( List.Zip({zile,a}), (x)=> x{1} <> ""))
in
b
Solving the challenge of Identify 2025 Palindromic Dates with Excel
Excel solution 1 for Identify 2025 Palindromic Dates, proposed by Bo Rydobon 🇹🇭:
=LET(
d,
SEQUENCE(
365,
,
"1jan"
),
f,
{"DMY",
"MDY",
"YMD"},
x,
TEXT(
d,
f
),
L,
LAMBDA(
a,
TOCOL(
IFS(
MID(
x,
2,
1
)&LEFT(
x
)=RIGHT(
x,
2
),
a
),
3
)
),
GROUPBY(
L(
d
),
L(
f
),
ARRAYTOTEXT,
,
0
)
)Excel solution 2 for Identify 2025 Palindromic Dates, proposed by John V.:
=LET(f,{"DMY","MDY","YMD"},d,45657+ROW(1:365),c,MAP(TEXT(d,f),LAMBDA(x,x=CONCAT(MID(x,7-ROW(1:6),1)))),FILTER(HSTACK(d,BYROW(IF(c,f,""),LAMBDA(r,TEXTJOIN(", ",,r)))),BYROW(c,OR)))Excel solution 3 for Identify 2025 Palindromic Dates, proposed by Kris Jaganah:
=LET(
a,
SEQUENCE(
365,
,
DATE(
2025,
1,
1
)
),
b,
DROP(
REDUCE(
"",
{"DMY",
"MDY",
"YMD"},
LAMBDA(
x,
y,
HSTACK(
x,
MAP(
TEXT(
a,
y
),
LAMBDA(
v,
IF(
BYROW(
MID(
v,
{6,
5,
4,
3,
2,
1},
1
),
CONCAT
)=v,
y,
""
)
)
)
)
)
),
,
1
),
c,
BYROW(
b,
LAMBDA(
y,
TEXTJOIN(
", ",
,
y
)
)
),
VSTACK(
{"Date",
"Format"},
FILTER(
HSTACK(
a,
c
),
c<>""
)
)
)Excel solution 4 for Identify 2025 Palindromic Dates, proposed by Julian Poeltl:
=LET(S,SEQUENCE(365,,DATE(2025,1,1)),M,MAP(S,LAMBDA(I,LET(D,DAY(I),M,MONTH(I),Y,RIGHT(YEAR(I),2),TEXTJOIN(",",,FILTER(HSTACK("DMY","MDY","YMD"),MAP(HSTACK(D&M&Y,M&D&Y,Y&M&D),LAMBDA(A,RIGHT(A,2)=MID(A,2,1)&LEFT(A,1)))))))),FILTER(HSTACK(S,M),ISTEXT(M)))Excel solution 5 for Identify 2025 Palindromic Dates, proposed by Timothée BLIOT:
=LET(S,SEQUENCE(365,,0)+DATE(2025,1,1),F,LAMBDA(n,TEXT(S,n)),D,{"MDY","DMY","YMD"},E,MAP(F(D),LAMBDA(x,CONCAT(MID(x,1+LEN(x)-SEQUENCE(LEN(x)),1))=x)),G,LAMBDA(n,TOCOL(IF(E,n,1/0),3)),I,SORT(GROUPBY(G(S),G(D),ARRAYTOTEXT,,0)),HSTACK(TEXT(TAKE(I,,1),"DD-MMM-YYYY"),TAKE(I,,-1)))Excel solution 6 for Identify 2025 Palindromic Dates, proposed by Oscar Mendez Roca Farell:
=LET(
s,
SEQUENCE(
365,
,
45658
),
f,
{"DMY",
"MDY",
"YMD"},
m,
MAP(
TEXT(
s,
f
),
LAMBDA(
a,
a=CONCAT(
MID(
a,
6-ROW(
1:5
),
1
)
)
)
),
FILTER(
HSTACK(
s,
BYROW(
IF(
m,
f,
""
),
LAMBDA(
r,
TEXTJOIN(
", ",
,
r
)
)
)
),
BYROW(
m,
OR
)
)
)Excel solution 7 for Identify 2025 Palindromic Dates, proposed by Sunny Baggu:
=LET(
_s, N("😊🆕 Year ✨🎉🕊☮") +
SEQUENCE(
1 + EOMONTH(DATE(2025, 12, 1), 0) - DATE(2025, 1, 1),
,
DATE(2025, 1, 1)
),
_dmy, TEXT(_s, "dmy"),
_mdy, TEXT(_s, "mdy"),
_ymd, TEXT(_s, "ymd"),
LET(
_e1, LAMBDA(rng,
MAP(
rng,
LAMBDA(a, --a = --CONCAT(MID(a, LEN(a) + 1 - SEQUENCE(LEN(a)), 1)))
)
),
_r, SORT(
VSTACK(
IFNA(HSTACK(FILTER(_s, _e1(_dmy)), "DMY"), "DMY"),
IFNA(HSTACK(FILTER(_s, _e1(_mdy)), "MDY"), "MDY"),
IFNA(HSTACK(FILTER(_s, _e1(_ymd)), "YMD"), "YMD")
)
),
_r1, TAKE(_r, , 1),
_r2, TAKE(_r, , -1),
_ur, UNIQUE(_r1),
HSTACK(_ur, MAP(_ur, LAMBDA(a, ARRAYTOTEXT(FILTER(_r2, _r1 = a)))))
)
)Excel solution 8 for Identify 2025 Palindromic Dates, proposed by Md. Zohurul Islam:
=LET(
sq,
SEQUENCE(
365,
,
DATE(
2025,
1,
1
)
),
D,
DAY(
sq
),
M,
MONTH(
sq
),
Y,
RIGHT(
YEAR(
sq
),
2
),
DMY,
D&M&Y,
MDY,
M&D&Y,
YMD,
Y&M&D,
rng,
HSTACK(
DMY,
MDY,
YMD
),
format,
HSTACK(
"DMY",
"MDY",
"YMD"
),
U,
MAP(
rng,
LAMBDA(
x,
LET(
a,
RIGHT(
x,
2
),
b,
MID(
x,
2,
1
),
c,
LEFT(
x,
1
),
d,
IF(
a=b&c,
1,
0
),
d
)
)
),
V,
BYROW(
U,
LAMBDA(
x,
ARRAYTOTEXT(
FILTER(
format,
x>0,
0
)
)
)
),
W,
FILTER(
HSTACK(
sq,
V
),
ISERR(
--V
)
),
W
)Excel solution 9 for Identify 2025 Palindromic Dates, proposed by JvdV –:
=LET(
s,
SEQUENCE(
365,
,
DATE(
2025,
1,
1
)
),
x,
{"DMY",
"MDY",
"YMD"},
y,
IFS(
REGEXTEST(
TEXT(
s,
x
),
"^((.)(?1)2|.?)$"
),
x
),
GROUPBY(
TOCOL(
IF(
ISNA(
y
),
z,
s
),
2
),
TOCOL(
y,
2
),
ARRAYTOTEXT,
,
0
)
)Solving the challenge of Identify 2025 Palindromic Dates with Python
Python solution 1 for Identify 2025 Palindromic Dates, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "621 Palindromic Dates in 2025.xlsx"
input = pd.read_excel(path, usecols="A", skiprows=1, nrows=12)
test = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=13)
date_range = pd.date_range(start="2025-01-01", end="2025-12-31")
df = pd.DataFrame({'Date': date_range})
df['year'], df['month'], df['day'] = df['Date'].dt.year - 2000, df['Date'].dt.month, df['Date'].dt.day
df['MDY'] = df['month'].astype(str) + df['day'].astype(str) + df['year'].astype(str)
df['DMY'] = df['day'].astype(str) + df['month'].astype(str) + df['year'].astype(str)
df['YMD'] = df['year'].astype(str) + df['month'].astype(str) + df['day'].astype(str)
df_long = df.melt(id_vars=['Date'], value_vars=['MDY', 'DMY', 'YMD'], var_name='Format', value_name='Value')
df_long['Rev'] = df_long['Value'].apply(lambda x: x[::-1])
result = df_long[df_long['Value'] == df_long['Rev']].groupby('Date')['Format'].apply(lambda x: ', '.join(x)).reset_index()
print(result.equals(test)) # True
Solving the challenge of Identify 2025 Palindromic Dates with R
R solution 1 for Identify 2025 Palindromic Dates, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/621 Palindromic Dates in 2025.xlsx"
input = read_excel(path, range = "A2:A14")
test = read_excel(path, range = "A2:B14")
result = seq.Date(as.Date("2025-01-01"), as.Date("2025-12-31"), by = "day") %>%
as_tibble() %>%
separate(value, c("year", "month", "day"), sep = "-", remove = F) %>%
mutate(year = as.numeric(year) - 2000,
month = as.numeric(month),
day = as.numeric(day),
value = as.POSIXct(value)) %>%
mutate(MDY = paste(month, day, year, sep = ""),
DMY = paste(day, month, year, sep = ""),
YMD = paste(year, month, day, sep = "")) %>%
select(Date = value, MDY, DMY, YMD) %>%
pivot_longer(cols = -Date, names_to = "Format", values_to = "Value") %>%
mutate(Rev = map_chr(Value, ~str_c(rev(str_split(.x, "")[[1]]), collapse = ""))) %>%
filter(Value == Rev) %>%
summarise(Format = str_c(Format, collapse = ", "), .by = Date)
all.equal(result, test, check.attributes = F)
#> [1] TRUE
&&&