An Isogram date is a two digits year date in which no digit occurs more than once. Ex. 2043-08-27 – Two digits year date is 430827 and all digits occur only once. 2013-08-17 is not an Isogram date as 1 occurs two times in 130817. For the dates in this century, find the count of Isogram dates, min and max Isogram dates in YYYY-MM-DD format.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 265
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Isogram Calendar Dates with Power Query
Power Query solution 1 for Find Isogram Calendar Dates, proposed by Bo Rydobon 🇹🇭:
let
d = List.Select(
List.Transform({2 .. 36525}, each Date.ToText(Date.From(_), "yy-MM-dd")),
each List.Count(List.Distinct(Text.ToList(_))) = 7
),
Ans = {List.Count(d), "20" & d{0}, "20" & List.Last(d)}
in
AnsPower Query solution 2 for Find Isogram Calendar Dates, proposed by Zoran Milokanović:
let
Source = let b = hashtag#date(2001, 1, 1), e = hashtag#date(2101, 1, 1) in List.Dates(b, Duration.Days(e - b), Duration.From(1)),
b = List.Select(Source, each List.IsDistinct(Text.ToList(Date.ToText(_, "yyMMdd"))))
in
[Count = List.Count(b), Min Date = b{0}, Max Date = List.Max(b)]
Power Query solution 3 for Find Isogram Calendar Dates, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = List.Dates (
hashtag#date ( 2000, 01, 01 ),
Number.From ( hashtag#date ( 2100, 12, 31 ) - hashtag#date ( 2000, 01, 01 ) ),
hashtag#duration ( 1, 0, 0, 0 )
),
Filter = List.Select (
Source,
each List.IsDistinct ( Text.ToList ( Date.ToText ( _, "yyMMdd" ) ) )
),
Return = [ Count = List.Count ( Filter ), Min = List.Min ( Filter ), Max = List.Max ( Filter ) ]
in
Return
Power Query solution 4 for Find Isogram Calendar Dates, proposed by Luan Rodrigues:
let
Fonte = [
Dates = List.Transform(
List.Select(
List.Transform(List.Dates(hashtag#date(2001,01,01),36525,hashtag#duration(1,0,0,0)),each
[
a = Date.ToText(_,"yyMMdd"),
b = {a,List.Count(List.Distinct(Text.ToList(a)))}
][b]
), (x)=> x{1}=6),each "20"&_{0}),
Count = List.Count(Dates),
#"Min Date" = List.Min(List.Transform(Dates,each Date.From(_,"en-US"))),
#"Max Date" = List.Max(List.Transform(Dates,each Date.From(_,"en-US")))
][[Count],[#"Min Date"],[#"Max Date"]]
in
Fonte
Power Query solution 5 for Find Isogram Calendar Dates, proposed by Brian Julius:
let
Source = Table.FromList( List.Transform({Number.From(hashtag#date(2000,1,1))..Number.From(hashtag#date(2098,12,31))}, each Date.From(_)), Splitter.SplitByNothing()),
ToDate = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
AddDate6Char = Table.AddColumn(ToDate, "Date6Char", each [
d =[Column1],
y = Text.End(Text.From( Date.Year(d)), 2),
m = Text.PadStart( Text.From( Date.Month(d)), 2, "0"),
x = Text.PadStart( Text.From( Date.Day(d)), 2, "0"),
z = y&m&x,
q = List.IsDistinct(Text.ToList( z))
][q]),
Filter = Table.SelectRows(AddDate6Char, each ([Date6Char] = true)),
Group = Table.Group(Filter, {"Date6Char"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"MinDate", each List.Min([Column1]), type nullable date}, {"MaxDate", each List.Max([Column1]), type nullable date}}),
Demote = Table.RemoveColumns( Table.DemoteHeaders(Group), "Column1"),
Transpose = Table.Transpose(Demote)
in
Transpose
Solving the challenge of Find Isogram Calendar Dates with Excel
Excel solution 1 for Find Isogram Calendar Dates, proposed by Bo Rydobon 🇹🇭:
=LET(
d,
TEXT(
SEQUENCE(
36525
),
"y-mm-dd"
),
f,
FILTER(
20&d,
MAP(
d,
LAMBDA(
n,
AND(
n=SUBSTITUTE(
n,
SEQUENCE(
10
)-1,
,
2
)
)
)
)
),
VSTACK(
ROWS(
f
),
@f,
TAKE(
f,
-1
)
)
)Excel solution 2 for Find Isogram Calendar Dates, proposed by Rick Rothstein:
=LET(d,
TEXT(("1900-1-1")+SEQUENCE(
36525,
,
0
),
"yymmdd"),
c,
MAP(
d,
LAMBDA(
n,
REDUCE(
0,
SEQUENCE(
10,
,
0
),
LAMBDA(
a,
x,
a+ISNUMBER(
SEARCH(
"*"&x&"*"&x&"*",
n
)
)
)
)
)
),
VSTACK(SUM(0+(c=0)),
TEXT(
20&TAKE(
FILTER(
d,
c=0
),
{1;-1}
),
"0000-00-00"
)))Excel solution 3 for Find Isogram Calendar Dates, proposed by John V.:
=LET(
d,
TEXT(
ROW(
36526:73050
),
"y-mm-dd"
),
b,
FILTER(
20&d,
MAP(
d,
LAMBDA(
x,
ROWS(
UNIQUE(
MID(
x,
ROW(
1:9
),
1
)
)
)=LEN(
x
)
)
)
),
VSTACK(
ROWS(
b
),
@b,
TAKE(
b,
-1
)
)
)Excel solution 4 for Find Isogram Calendar Dates, proposed by محمد حلمي:
=LET(
x,
TEXT(
SEQUENCE(
36525
)+36525,
"yymmdd"
),
v,
--BYROW(
MID(
x,
SEQUENCE(
,
6
),
1
),
LAMBDA(
a,
SUM(
--EXACT(
a,
TOCOL(
a
)
)
)=6
)
),
VSTACK(
SUM(
v
),
20&XLOOKUP(
1,
v,
x,
,
,
{1;-1}
)
)
)Excel solution 5 for Find Isogram Calendar Dates, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_strt,
B3,
_end,
B4,
_diff,
_end - _strt + 1,
_seq,
TEXT(
SEQUENCE(
_diff,
,
_strt
),
"emmdd"
),
_lst6,
RIGHT(
_seq,
6
),
_calc,
MAP(
_lst6,
LAMBDA(
a,
N(
ROWS(
UNIQUE(
MID(
a,
SEQUENCE(
6
),
1
)
)
) = 6
)
)
),
_r,
SUM(
_calc
),
_r
)Excel solution 6 for Find Isogram Calendar Dates, proposed by Timothée BLIOT:
=LET(
A,
TEXT(
SEQUENCE(
DATE(
2100,
1,
1
)-DATE(
2000,
1,
1
),
,
DATE(
2000,
1,
1
)
),
"YYMMDD"
),
B,
FILTER(
A,
MAP(
A,
LAMBDA(
x,
LEN(
CONCAT(
UNIQUE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)*1
)
)
)=6
)
)
),
C,
VSTACK(
TAKE(
B,
1
),
TAKE(
B,
-1
)
),
VSTACK(
COUNTA(
B
),
20&LEFT(
C,
2
)&"-"&MID(
C,
3,
2
)&"-"&RIGHT(
C,
2
)
)
)Excel solution 7 for Find Isogram Calendar Dates, proposed by Hussein SATOUR:
=LET(
a,
ROW(
1:36525
) + 36525,
b,
FILTER(
a,
MAP(
TEXT(
a,
"yymmdd"
),
LAMBDA(
x,
COUNTA(
UNIQUE(
MID(
x,
SEQUENCE(
6
),
1
)
)
) = 6
)
)
),
VSTACK(
COUNT(
b
),
MIN(
b
),
MAX(
b
)
)
)Excel solution 8 for Find Isogram Calendar Dates, proposed by Sunny Baggu:
=LET(
Seq, SEQUENCE(B4 - B3 + 1, , B3),
SUM(
N(
MAP(
TEXT(Seq, "yymmdd"),
LAMBDA(a, ROWS(UNIQUE(MID(a, SEQUENCE(LEN(a)), 1))) = LEN(a))
)
)
)
)Excel solution 9 for Find Isogram Calendar Dates, proposed by LEONARD OCHEA 🇷🇴:
=LET(t;NOW();m;TEXT(SEQUENCE(36525;;36525);"yymmdd");n;--(MMULT(--(LEN(SUBSTITUTE(m;MID(m;SEQUENCE(;6);1);""))<5);SEQUENCE(6)^0)=0);s;SUM(n);fl;36524+XMATCH(1;n;;VSTACK(1;-1));VSTACK(s;TEXT(fl;"yyyy-mm-dd");"Time >>> "&TEXT(NOW()-t;"[s].000s")))Excel solution 10 for Find Isogram Calendar Dates, proposed by Abdallah Ally:
=LET(
a,
SEQUENCE(
36525,
,
36526
),
b,
TEXT(
a,
"yymmdd"
),
c,
FILTER(
a,
MAP(
b,
LAMBDA(
x,
COUNTA(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)=COUNTA(
UNIQUE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
)
)
)
),
HSTACK(
{"Count";"Min date";"Max Date"},
VSTACK(
COUNTA(
c
),
TEXT(
MIN(
c
),
"yyyy-mm-dd"
),
TEXT(
MAX(
c
),
"yyyy-mm-dd"
)
)
)
)Excel solution 11 for Find Isogram Calendar Dates, proposed by JvdV –:
=LET(n,TEXT(ROW(36526:73050),"y-mm-dd"),m,FILTER(20&n,REDUCE(n,ROW(1:10)-1,LAMBDA(x,y,SUBSTITUTE(x,y,,1)))="--"),VSTACK(ROWS(m),@m,TAKE(m,-1)))
Or a little shorter:
=LET(n,TEXT(ROW(36526:73050),"y-mm-dd"),s,ROW(1:10),m,FILTER(20&n,MMULT(--ISERR(FIND(TOROW(s-1),n)),s^0)=4),VSTACK(ROWS(m),@m,TAKE(m,-1)))Excel solution 12 for Find Isogram Calendar Dates, proposed by Pieter de Bruijn:
=LET(d,TEXT(SEQUENCE(36525),"y-mm-dd"),f,20&FILTER(d,MAP(SUBSTITUTE(d,"-",""),LAMBDA(x,LET(z,MID(x,SEQUENCE(6),1),SUM(--(z=TOROW(z)))=6)))),VSTACK(ROWS(f),@f,TAKE(f,-1)))Excel solution 13 for Find Isogram Calendar Dates, proposed by Giorgi Goderdzishvili:
=LET(
days,
DATEDIF(
DATE(
2000,
1,
1
),
DATE(
2099,
12,
31
),
"d"
)+1,
arr,
DATE(
2000,
1,
0
)+SEQUENCE(
days
),
fr,
TEXT(
arr,
"yymmdd"
),
sub,
LEN(
fr
)-LEN(
SUBSTITUTE(
fr,
SEQUENCE(
,
10,
9,
-1
),
""
)
),
br,
--BYROW(sub,
LAMBDA(x,
SUM(--(x>1))=0)),
mx,
TEXT(
MAX(
IF(
br,
arr,
""
)
),
"yyyy-mm-dd"
),
mn,
TEXT(
MIN(
IF(
br,
arr,
""
)
),
"yyyy-mm-dd"
),
HSTACK(
VSTACK(
"Count",
"Min Date",
"Max Date"
),
VSTACK(
SUM(
br
),
mn,
mx
)
))Excel solution 14 for Find Isogram Calendar Dates, proposed by Daniel Garzia:
=LET(
d,
TEXT(
ROW(
36526:73050
),
"yyy-mm-dd"
),
f,
FILTER(
d,
MAP(
d,
LAMBDA(
x,
LET(
r,
RIGHT(
x,
8
),
ROWS(
UNIQUE(
MID(
r,
SEQUENCE(
8
),
1
)
)
)+1=LEN(
r
)
)
)
)
),
VSTACK(
ROWS(
f
),
TAKE(
f,
{1;-1}
)
)
)Excel solution 15 for Find Isogram Calendar Dates, proposed by samir tobeil:
=LET(
D,
SEQUENCE(
36525,
,
36526
),
S,
MAP(
D,
LAMBDA(
x,
COUNTA(
UNIQUE(
MID(
TEXT(
x,
"yyyymmdd"
),
ROW(
3:8
),
1
)
)
)
)
),
A,
FILTER(
D,
S=6
),
VSTACK(
COUNTA(
A
),
MIN(
A
),
MAX(
A
)
)
)Excel solution 16 for Find Isogram Calendar Dates, proposed by Md Ismail Hosen:
=LET(
AllDates,
SEQUENCE(
36525,
1,
"2000-01-01",
1
),
IsIsoGram,
MAP(
TEXT(
AllDates,
"YYMMDD"
),
LAMBDA(
a,
ROWS(
UNIQUE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)
) = LEN(
a
)
)
),
OnlyIsoGramDate,
FILTER(
AllDates,
IsIsoGram
),
Count,
ROWS(
OnlyIsoGramDate
),
MinDate,
TEXT(
MIN(
OnlyIsoGramDate
),
"YYYY-MM-DD"
),
MaxDate,
TEXT(
MAX(
OnlyIsoGramDate
),
"YYYY-MM-DD"
),
HSTACK(
{"Count";"Min Date";"Max Date"},
VSTACK(
Count,
MinDate,
MaxDate
)
)
)Excel solution 17 for Find Isogram Calendar Dates, proposed by Mungunbayar Bat-Ochir:
=LET(
startDate; DATE(2000;1;1);
allDates; SEQUENCE(DATE(2099;12;31)-startDate+1;;startDate);
isIsogram; BYROW(allDates;LAMBDA(row;ROWS(UNIQUE(MID(TEXT(row;"jjMMtt");SEQUENCE(6);1)))=6));
isogram; FILTER(allDates;isIsogram);
count; ROWS(isogram);
minDate; TAKE(isogram;1);
maxDate; TAKE(isogram;-1);
VSTACK(count;minDate;maxDate)
)Excel solution 18 for Find Isogram Calendar Dates, proposed by Harry Seiders:
=LET(days,
DATE(
2099,
12,
31
)-DATE(
2000,
1,
1
)+1,
S,
TEXT(
DATE(
2000,
1,
0
)+SEQUENCE(
days
),
"YYMMDD"
),
X,
MID(
S,
SEQUENCE(
,
6
),
1
),
E,
BYROW(
X,
LAMBDA(
Z,
COUNTA(
UNIQUE(
Z,
TRUE
)
)
)
),
tot,
SUM(--(E=6)),
Fil,
--FILTER(
S,
E=6
),
mins,
"20"&TEXT(
MIN(
Fil
),
"##-##-##"
),
Maxs,
"20"&TEXT(
MAX(
Fil
),
"##-##-##"
),
out,
VSTACK(
tot,
mins,
Maxs
),
out)Excel solution 19 for Find Isogram Calendar Dates, proposed by Deepak Dalal:
= SUM(MAP(TEXT(SEQUENCE(31296,1, 41389, 1), "yymmdd"), LAMBDA(a, IF(LEN(a) = LEN(CONCAT(UNIQUE(MID(a, SEQUENCE(LEN(a)), 1)))), 1, 0))))&&