List all dates in 21st century which are perfect square dates. Consider dates in YYYYMMDD format to work out the answer. What is a perfect square? – Ex – 25 is a perfect square but 26 is not a perfect square as SQRT(25) = 5 but SQRT(26) = 5.099
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 292
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Perfect Square Dates with Power Query
Power Query solution 1 for Find Perfect Square Dates, proposed by Bo Rydobon 🇹🇭:
let
Source = List.RemoveNulls(
List.Transform({4472 .. 4582}, each try Date.From(Text.From(_ * _)) otherwise null)
)
in
SourcePower Query solution 2 for Find Perfect Square Dates, proposed by Zoran Milokanović:
let
Source = {20010101, 21001231},
Q = (n) => Int64.From(Number.Sqrt(n)),
P = (n) => n * n,
IsDate = (n) => n <> (try Date.FromText(Text.From(n)) otherwise n),
S = List.Select(List.Transform({Q(Source{0}) .. Q(Source{1})}, P), IsDate)
in
SPower Query solution 3 for Find Perfect Square Dates, proposed by Zoran Milokanović:
let
Source = {20010101 .. 21001231},
Q = (n) => Int64.From(Number.Sqrt(n)),
IsDate = (n) => n <> (try Date.FromText(Text.From(n)) otherwise n),
S = List.Select(Source, each _ = Q(_) * Q(_) and IsDate(_))
in
SPower Query solution 4 for Find Perfect Square Dates, proposed by Aditya Kumar Darak 🇮🇳:
let
Start = Number.RoundUp(Number.Power(20000101, 0.5)),
End = Int64.From(Number.Power(20991231, 0.5)),
Generate = List.Transform(
{Start .. End},
each try Date.ToText(Date.From(Text.From(Number.Power(_, 2))), "YYYYMMDD") otherwise null
),
Return = List.RemoveNulls(Generate)
in
ReturnPower Query solution 5 for Find Perfect Square Dates, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = List.Transform(List.Select(List.Transform({Number.From(hashtag#date(2000,01,01))..Number.From(hashtag#date(2099,12,31))}, each
let
a = Number.From(Date.ToText(Date.From(_), [Format = "YYYYMMDD"])),
b = Number.Mod(Number.Mod(a, Number.Sqrt(a)), Number.Sqrt(a))
in {a,b}), each _{1}=0), each _{0})
in
Source
Power Query solution 6 for Find Perfect Square Dates, proposed by Luan Rodrigues:
let
Fonte = List.RemoveNulls(List.Transform(List.Select(List.Transform({Number.From(Date.ToText(hashtag#date(2001,01,01),"YYYYMMDD"))..Number.From(Date.ToText(hashtag#date(2100,12,31),"YYYYMMDD"))}, each {_}&{Number.Sqrt(_)}), each _{1} = Int64.From(_{1}) ), each try Date.From(Text.From(_{0})) otherwise null))
in
Fonte
Power Query solution 7 for Find Perfect Square Dates, proposed by Ramiro Ayala Chávez:
let
Origen = {Number.From(hashtag#date(2001,01,01))..Number.From(hashtag#date(2100,12,31))},
a = List.Transform(Origen,each Date.From(_)),
b = List.Transform(a, each Date.ToText(_, [Format="YYYYMMDD"])),
c = Table.FromList(b, Splitter.SplitByNothing()),
d = Table.TransformColumnTypes(c,{{"Column1", Int64.Type}}),
e = Table.AddColumn(d, "Sqrt", each Number.Sqrt([Column1])),
f = Table.AddColumn(e, "Answer Expected", each if [Sqrt]=Int64.From([Sqrt]) then [Column1] else null)[[Answer Expected]],
Sol = Table.SelectRows(f, each ([Answer Expected] <> null))
in
Sol
Power Query solution 8 for Find Perfect Square Dates, proposed by Rafael González B.:
let
Source = {Number.From(hashtag#date(2001,1,1))..Number.From(hashtag#date(2100,12,31))},
Fx_PSqrt = (Num as number) =>
let
n = Num,
a = Date.From(n),
b = Date.ToText(a, "YYYYMMDD"),
c = Number.From(b),
d = Number.Sqrt(c),
e = Number.Mod(c,d),
f = e = 0
in
f,
Fx_DF = (N as number) =>
let
x = N,
y = Date.From(x),
z = Date.ToText(y, "YYYYMMDD")
in
z,
Select = List.Select(Source, each Fx_PSqrt(_)),
Result = List.Transform(Select, each Fx_DF(_))
in
Result
Power Query solution 9 for Find Perfect Square Dates, proposed by Kalyan Kumar Reddy Kethireddy:
let
Source = {Number.From(hashtag#date(2001,01,01))..Number.From(hashtag#date(2100,12,31))},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Dates = Table.TransformColumns(#"Converted to Table",{"Column1", each Number.From(Date.ToText(Date.From(_),[Format="YYYYMMDD"]))}),
Expected = Table.SelectRows(Dates, each let
a = [Column1],
b = Number.Sqrt([Column1]),
c = Number.RoundUp(b),
d = c*c
in
a = d)
in
Expected
Solving the challenge of Find Perfect Square Dates with Excel
Excel solution 1 for Find Perfect Square Dates, proposed by Bo Rydobon 🇹🇭:
=TEXT(TOCOL(--TEXT(SEQUENCE(111,,4472)^2,"0-00-00"),3),"emmdd")Excel solution 2 for Find Perfect Square Dates, proposed by Rick Rothstein:
=LET(
d,
INT(
SQRT(
{20000101,
20991231}
)
),
TOCOL(
0+TEXT(
SEQUENCE(
SUM(
d*{-1,
1}
),
,
MIN(
d
)
)^2,
"0-00-00"
),
2
)
)Excel solution 3 for Find Perfect Square Dates, proposed by John V.:
Hi everyone!
One (Python) option could be:
Blessings!Excel solution 4 for Find Perfect Square Dates, proposed by John V.:
=LET(d,
TEXT(
ROW(
36526:73050
),
"emmdd"
),
TOCOL(d/(MOD(
d^0.5,
1
)=0),
2))Excel solution 5 for Find Perfect Square Dates, proposed by محمد حلمي:
=LET(
d,
TEXT(
SEQUENCE(
100*365,
,
"1/1/2000"
),
"emmdd"
),
FILTER(
d,
MOD(
d^0.5,
1
)=0
)
)Excel solution 6 for Find Perfect Square Dates, proposed by Kris Jaganah:
=LET(a,
TEXT(
DATE(
2000,
1,
0
)+SEQUENCE(
36525
),
"yyyymmdd"
),
b,
a^0.5,
TEXT(TOCOL(a/(INT(
b
)=b),
3),
"#"))Excel solution 7 for Find Perfect Square Dates, proposed by Timothée BLIOT:
=LET(A,
(TEXT(
SEQUENCE(
DATE(
2100,
1,
1
)-DATE(
2000,
1,
1
),
,
DATE(
2000,
1,
1
)
),
"YYYYMMDD"
)*1),
FILTER(
A,
A^0.5=INT(
A^0.5
)
))Excel solution 8 for Find Perfect Square Dates, proposed by Hussein SATOUR:
=LET(
a,
TEXT(
SEQUENCE(
36525,
,
36526
),
"emmdd"
),
FILTER(
a,
SQRT(
a
) = INT(
SQRT(
a
)
)
)
)Excel solution 9 for Find Perfect Square Dates, proposed by Sunny Baggu:
=LET(
_list,
TEXT(
SEQUENCE(
365 * 100,
,
36526
),
"yyyymmdd"
),
TOCOL(_list / (SQRT(
_list
) = INT(
SQRT(
_list
)
)),
3)
)Excel solution 10 for Find Perfect Square Dates, proposed by Abdallah Ally:
=LET(
a,
TEXT(
SEQUENCE(
73050-36526+1,
,
36526
),
"yyyymmdd"
),
FILTER(
a,
LEN(
SQRT(
a
)
)=LEN(
INT(
SQRT(
a
)
)
)
)
)Excel solution 11 for Find Perfect Square Dates, proposed by Pieter de Bruijn:
=LET(
s,
--TEXT(
SEQUENCE(
36524,
,
36526
),
"emmdd"
),
r,
SQRT(
s
),
FILTER(
s,
INT(
r
)=r
)
)Excel solution 12 for Find Perfect Square Dates, proposed by Nicolas Micot:
=LET(
_nbJours;
DATE(
2100;
12;
31
)-DATE(
2001;
1;
1
)+1;
_dates;
TEXTE(
SEQUENCE(
_nbJours;
;
DATE(
2001;
1;
1
)
);
"aaaammjj"
)+0;
FILTRE(
_dates;
MOD(
RACINE(
_dates
);
1
)=0
)
)Excel solution 13 for Find Perfect Square Dates, proposed by Giorgi Goderdzishvili:
=LET(
frs,
DATE(
2000,
1,
1
),
lst,
DATE(
2099,
12,
31
),
sq,
SEQUENCE(
lst-frs+1,
,
frs
),
frm,
TEXT(
sq,
"yyyymmdd"
),
chck,
SQRT(
frm
)=INT(
SQRT(
frm
)
),
flt,
--FILTER(
frm,
chck
),
flt
)Excel solution 14 for Find Perfect Square Dates, proposed by Daniel Garzia:
=LET(
d,
TEXT(
ROW(
36526:73415
),
"YYYYMMDD"
),
s,
d^0.5,
TOCOL(
d/IF(
s-INT(
s
),
,
1
),
2
)
)Excel solution 15 for Find Perfect Square Dates, proposed by Diarmuid Early:
=LET(frst,DATE(2000,1,1),
lst,DATE(2099,12,31),
toTest,SEQUENCE(lst-frst+1,,frst),
asTxt,TEXT(toTest,"yyyymmdd"),
FILTER(asTxt,INT(SQRT(asTxt))^2-asTxt=0))
The alternative: instead of looking at all possible dates, look at all possible squares (in the range), and see which are dates - something like this:
=LET(frst,ROUNDUP(SQRT(20000101),0),
lst,INT(SQRT(20991231)),
toTest,SEQUENCE(lst-frst+1,,frst)^2,
FILTER(toTest,TEXT(DATE(LEFT(toTest,4),MID(toTest,5,2),RIGHT(toTest,2)),"yyyymmdd")-toTest=0))
This is much faster, both because squaring is much easier than taking square roots, and because there are many fewer squares in the range (in this case, the list to test is 109 squares vs 36,525 dates).
I did a timing test, and the first one took about 0.1-0.2 seconds, the second one took <0.01 seconds (the shortest time I can measure). I scaled them both up to try any date in this millennium, and the first one took 2 seconds, while the second one still took <0.01 seconds (the two answers did agree though!)Excel solution 16 for Find Perfect Square Dates, proposed by samir tobeil:
=LET(
a,
TEXT(
"2000/1/1"+SEQUENCE(
36524
)-1,
"yyyymmdd"
),
FILTER(
a,
BYROW(
a,
LAMBDA(
x,
MOD(
x^0.5,
1
)
)
)=0
)
)Excel solution 17 for Find Perfect Square Dates, proposed by Mungunbayar Bat-Ochir:
=LET(
start_date;
DATE(
2000;
1;
1
);
end_date;
DATE(
2099;
12;
31
);
dates;
VALUE(
TEXT(
SEQUENCE(
end_date-start_date+1;
;
start_date
);
"[$-0007]jjjjMMtt"
)
);
bool;
SQRT(
dates
)=INT(
SQRT(
dates
)
);
FILTER(
dates;
bool
)
)Excel solution 18 for Find Perfect Square Dates, proposed by Jeff Blakley:
=LET(beg, INT(SQRT(20010101)),
end, INT(SQRT(21001231)),
dates, --TEXT(SEQUENCE(end-beg+1,,beg)^2, "0000/00/00"),
FILTER(dates, ISNUMBER(dates)))Solving the challenge of Find Perfect Square Dates with Python
Python solution 1 for Find Perfect Square Dates, proposed by Bo Rydobon 🇹🇭:
from datetime import datetime as dt
def ymd(d):
try: return dt.strptime(d,'%Y%m%d')
except: return 0
[v for d in range(4472,4583) if ymd((v:=str(d**2)))!=0]
Solving the challenge of Find Perfect Square Dates with R
_x000D_
R solution 1 for Find Perfect Square Dates, proposed by Konrad Gryczan, PhD:
Inspired by Diarmuid Early I tried to recreate approach "from squares to dates", along with my own "from dates to squares"
And here they are:
library(tidyverse)
### Approach from dates to squares
dates = seq.Date(from = as.Date("2000-01-01"),
to = as.Date("2099-12-31"),
by = "1 day")
int_date = as.integer(format(dates, "%Y%m%d"))
result = int_date %>%
keep( ~ {
root = sqrt(.x)
root == as.integer(root)
})
### Approach from squares to dates
min_val <- 20000101
max_val <- 20991231
min_root <- ceiling(sqrt(min_val))
max_root <- floor(sqrt(max_val))
perfect_squares <- seq(min_root, max_root) %>%
map(~ .x * .x) %>%
keep(~ {
ymd <- as.character(.x)
year <- as.integer(substr(ymd, 1, 4))
month <- as.integer(substr(ymd, 5, 6))
day <- as.integer(substr(ymd, 7, 8))
tryCatch({
as.Date(paste(year, month, day, sep = "-"))
TRUE
}, error = function(e) FALSE)
}) %>%
unlist()
_x000D_
Solving the challenge of Find Perfect Square Dates with Excel VBA
_x000D_
Excel VBA solution 1 for Find Perfect Square Dates, proposed by Emad Falahnezhad:
Sub quaddate()
Ro = 2
For y = 2000 To 2099
For m = 1 To 12
For d = 1 To 31
Number = y * 10000 + m * 100 + d
sqrtValue = Sqr(Number)
FixNd = Fix(sqrtValue) ^ 2
If FixNd = Number Then
Sheet1.Cells(Ro, 1).Value = Number
Ro = Ro + 1
End If
Next
Next
Next
End Sub
_x000D_
&&