List the dates in YYYY-MM-DD format from 21st century i.e. between 1-Jan-2000 to 31-Dec-2099 where MM * DD = YY For example: 2008-04-02 where 04 * 02 = 08
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 262
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List Dates Matching Product Rule with Power Query
Power Query solution 1 for List Dates Matching Product Rule, proposed by Bo Rydobon 🇹🇭:
let
Date = List.Sort(List.RemoveNulls(List.Combine(List.Transform({1..12}, (m)=> List.Transform({1..31},(d)=> let y = m*d in if y> 99 then null else try hashtag#date(2000+y,m,d) otherwise null )))))
in
Date
Power Query solution 2 for List Dates Matching Product Rule, proposed by Zoran Milokanović:
let
Source = let b = hashtag#date(2001, 1, 1), e = hashtag#date(2100, 1, 1) in List.Dates(b, Duration.Days(e - b), Duration.From(1)),
d = (x) => [f = (y) => Number.From(Date.ToText(x, y))][f],
S = List.Select(Source, each d(_)("MM") * d(_)("dd") = d(_)("yy"))
in
S
OR
let
Source = let b = hashtag#date(2001, 1, 1), e = hashtag#date(2100, 1, 1) in List.Dates(b, Duration.Days(e - b), Duration.From(1))
in
List.Select(Source, each let f = (y) => Number.From(Date.ToText(_, y)) in f("MM") * f("dd") = f("yy"))
Power Query solution 3 for List Dates Matching Product Rule, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = List.TransformMany (
{ 1 .. 12 },
( x ) => List.Select ( { 1 .. 31 }, ( f ) => f * x < 100 ),
( x, y ) => try hashtag#date ( x * y + 2000, x, y ) otherwise null
),
Return = List.Sort ( List.RemoveNulls ( Source ) )
in
Return
Power Query solution 4 for List Dates Matching Product Rule, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = List.Transform({Number.From(hashtag#date(2001,01,01))..Number.From(hashtag#date(2099,12,31))}, each Date.From(_)),
Dates = List.Select(Source, each Date.Day(_)*Date.Month(_)=(Date.Year(_)-2000))
in
Dates
2/2
let
Source = List.Transform({Number.From(hashtag#date(2001,01,01))..Number.From(hashtag#date(2099,12,31))}, each Date.From(_)),
Dates = List.Select(Source, each Date.Day(_)*Date.Month(_)=(Date.Year(_)-2000)),
Sol = Table.FromColumns({List.Transform(Dates, each Date.ToText(_, "yyyy-MM-dd"))}, {"Dates"})
in
Sol
Power Query solution 5 for List Dates Matching Product Rule, proposed by Chris A.:
let
start = hashtag#date(2000,1,1),
end = hashtag#date(2099, 12, 31),
dates =List.Dates(start, Duration.TotalDays(end-start) + 1,hashtag#duration(1,0,0,0)),
select = List.Select(dates,each Date.Month(_) * Date.Day(_) = (Date.Year(_)-2000))
in
select
Nice challenge, ty!
Solving the challenge of List Dates Matching Product Rule with Excel
Excel solution 1 for List Dates Matching Product Rule, proposed by Rick Rothstein:
=LET(
d,
TEXT(
SEQUENCE(
36525,
,
"2000-1-1"
),
"yyyy-mm-dd"
),
FILTER(
d,
MONTH(
d
)*DAY(
d
)=0+TEXT(
d,
"yy"
)
)
)
But this will work in any locale...
=LET(
d,
TEXT(
SEQUENCE(
36525,
,
"2000-1-1"
),
"yyyy-mm-dd"
),
FILTER(
d,
MONTH(
d
)*DAY(
d
)=MOD(
YEAR(
d
),
100
)
)
)Excel solution 2 for List Dates Matching Product Rule, proposed by محمد حلمي:
=LET(d,SEQUENCE(36525,,"1/1/2000"),
FILTER(d,MONTH(d)*DAY(d)=YEAR(d)-2000))Excel solution 3 for List Dates Matching Product Rule, proposed by Kris Jaganah:
=LET(a,
TEXT(
DATE(
2000,
1,
0
)+SEQUENCE(
36525
),
"yyyy-mm-dd"
),
FILTER(a,
MID(
a,
3,
2
)/(MID(
a,
6,
2
)*RIGHT(
a,
2
))=1))Excel solution 4 for List Dates Matching Product Rule, proposed by Timothée BLIOT:
=LET(
A,
SEQUENCE(
DATE(
2100,
1,
1
)-DATE(
2000,
1,
1
),
,
DATE(
2000,
1,
1
)
),
TEXT(
FILTER(
A,
MONTH(
A
)*DAY(
A
)=--RIGHT(
YEAR(
A
),
2
)
),
"YYYY-MM-DD"
)
)Excel solution 5 for List Dates Matching Product Rule, proposed by Hussein SATOUR:
=LET(a, "1/1/2000", b, a + SEQUENCE(36525,,0),
TEXT(FILTER(b, MONTH(b)*DAY(b) =--TEXT(b, "yy")), "yyyy-mm-dd"))Excel solution 6 for List Dates Matching Product Rule, proposed by Sunny Baggu:
=LET(
s,
DATE(
2000,
1,
1
),
e,
DATE(
2099,
12,
31
),
list,
SEQUENCE(
e - s + 1,
,
s
),
TEXT(
TOCOL(
IFS(MAP(list,
LAMBDA(a,
TEXT(
a,
"d"
) * TEXT(
a,
"m"
) = 1 * (TEXT(
a,
"yy"
)))),
list,
1,
NA()),
3
),
"yyy-mm-dd"
)
)Excel solution 7 for List Dates Matching Product Rule, proposed by Brian Julius:
=
CALENDAR(
DATE(
2000,
1,
1
),
DATE(
2099,
12,
31
)
)
VAR CalTable =
ADDCOLUMNS(
DateList,
"@MonNum",
MONTH(
[Date]
),
"@DayNum",
DAY(
[Date]
),
"@Yr2Digit",
YEAR(
[Date]
) - 2000
)
VAR AnswTable =
ADDCOLUMNS(
CalTable,
"@MeetsCond",
IF( ( [@DayNum] * [@MonNum] = [@Yr2Digit] ),
1,
0 )
)
VAR Result =
SELECTCOLUMNS(
FILTER(
AnswTable,
[@MeetsCond] = 1
),
"@Answer",
[Date]
)Excel solution 8 for List Dates Matching Product Rule, proposed by Anshu Bantra:
=LET(
start_date, DATE(2000, 1, 1),
end_date, DATE(3000, 12, 31),
dates, SEQUENCE(end_date - start_date + 1, , start_date),
FILTER(
dates,
MONTH(dates) * DAY(dates) = RIGHT(YEAR(dates), 2) * 1
)
)Excel solution 9 for List Dates Matching Product Rule, proposed by JvdV –:
=LET(x,TEXT(ROW(36526:73050),"e-mm-dd"),FILTER(x,DAY(x)*MONTH(x)=--MID(x,3,2)))Excel solution 10 for List Dates Matching Product Rule, proposed by Julien Lacaze:
=LET(dates,SEQUENCE(DATE(2099,12,31)-DATE(2000,1,1)+1,,DATE(2000,1,1)),
TEXT(FILTER(dates,MAP(dates,
LAMBDA(d,MONTH(d)*DAY(d)+2000=YEAR(d)))),"yyyy-mm-dd"))Excel solution 11 for List Dates Matching Product Rule, proposed by Nicolas Micot:
=LET(_dates;DATE(2000;1;1)+SEQUENCE(36525;;0);
FILTRE(_dates;MAP(_dates;LAMBDA(l_dates;JOUR(l_dates)*MOIS(l_dates)=DROITE(ANNEE(l_dates);2)*1))))Excel solution 12 for List Dates Matching Product Rule, proposed by Giorgi Goderdzishvili:
=LET(
days,
DATEDIF(
DATE(
2000,
1,
1
),
DATE(
2099,
12,
31
),
"d"
)+1,
arr,
DATE(
2000,
1,
0
)+SEQUENCE(
days
),
chck,
MAP(arr,
LAMBDA(x,
MONTH(
x
)*(DAY(
x
))=(--RIGHT(
YEAR(
x
),
2
)))),
TEXT(
FILTER(
arr,
chck
),
"yyyy-mm-dd"
))Excel solution 13 for List Dates Matching Product Rule, proposed by Daniel Garzia:
=LET(
b,
36525,
d,
SEQUENCE(
b,
,
1+b
),
TEXT(
FILTER(
d,
--RIGHT(
YEAR(
d
),
2
)=MONTH(
d
)*DAY(
d
)
),
"yyy-mm-dd"
)
)Excel solution 14 for List Dates Matching Product Rule, proposed by samir tobeil:
=LET(S,SEQUENCE(DATEDIF("2000/1/1","2099/1/1","D"),,DATEVALUE("1/1/2000")),FILTER(S,YEAR(S)=2000+DAY(S)*MONTH(S)))Excel solution 15 for List Dates Matching Product Rule, proposed by Md Ismail Hosen:
=LET(
fx_OneYear,
LAMBDA(
ForYear,
LET(
_Divisor,
FILTER(
SEQUENCE(
31
),
NOT(
MOD(
RIGHT(
ForYear,
2
) * 1,
SEQUENCE(
31
)
)
)
),
_ValidMonthAndDayCombo,
TOCOL(
IF(
_Divisor * TOROW(
_Divisor
) = RIGHT(
ForYear,
2
) * 1,
_Divisor & "-" & TOROW(
_Divisor
),
NA()
),
3
),
_FilterOutInValidMonth,
FILTER(
_ValidMonthAndDayCombo,
MAP(
_ValidMonthAndDayCombo,
LAMBDA(
a,
LET(
MonthNumber,
TEXTBEFORE(
a,
"-"
) * 1,
DayNumber,
TEXTAFTER(
a,
"-"
),
IsMonthValid,
MonthNumber <= 12,
Date,
DATE(
ForYear,
MonthNumber,
DayNumber
),
MonthEndDate,
EOMONTH(
DATE(
ForYear,
MonthNumber,
1
),
0
),
IsDateValid,
Date <= MonthEndDate,
Result,
AND(
IsMonthValid,
IsDateValid
),
Result
)
)
)
),
_Result,
IFERROR(
MAP(
_FilterOutInValidMonth,
LAMBDA(
a,
DATE(
ForYear,
TEXTBEFORE(
a,
"-"
) * 1,
TEXTAFTER(
a,
"-"
) * 1
)
)
),
""
),
_Result
)
),
Seq,
SEQUENCE(
99,
,
2001
),
Result,
REDUCE(
"",
Seq,
LAMBDA(
a,
v,
VSTACK(
a,
fx_OneYear(
v
)
)
)
),
FinalResult,
TEXT(
FILTER(
Result,
Result <> ""
),
"YYYY-MM-DD"
),
FinalResult
)Excel solution 16 for List Dates Matching Product Rule, proposed by Mungunbayar Bat-Ochir:
=LET(
dates;
"01.01.2000"+SEQUENCE(
1+"31.12.2099"-"01.01.2000";
;
0
);
m;
MONTH(
dates
);
d;
DAY(
dates
);
y;
RIGHT(
YEAR(
dates
);
2
)*1;
bool;
m*d=y;
FILTER(
dates;
bool
)
)Excel solution 17 for List Dates Matching Product Rule, proposed by Henriette Hamer:
=LET(
_startdate;
DATE(
2000;
1;
1
);
_enddate;
DATE(
2100;
1;
1
);
_sequence;
SEQUENCE(
_enddate-_startdate;
;
_startdate
);
TEXT(
FILTER(
_sequence;
TEXT(
_sequence;
"MM"
)*1*TEXT(
_sequence;
"DD"
)*1=TEXT(
_sequence;
"JJ"
)*1
);
"jjjj-mm-dd"
)
)_x000D_
Excel solution 18 for List Dates Matching Product Rule, proposed by Ricardo Alexis Domínguez Hernández:
=LET(
dates,
SEQUENCE(
73050-36892+1,
,
36892
),
FILTER(
dates,
MONTH(
dates
)*DAY(
dates
)=RIGHT(
YEAR(
dates
),
2
)*1=TRUE
)
)_x000D_
_x000D_
Excel solution 19 for List Dates Matching Product Rule, proposed by Narayanan J 🇮🇳:
=LET(
st,
DATE(
2000,
1,
1
),
dt,
SEQUENCE(
DATE(
2099,
12,
31
)-st+1,
1,
st
),
FILTER(
TEXT(
dt,
"e-MM-DD"
),
YEAR(
dt
)-2000=MONTH(
dt
)*DAY(
dt
)
)
)
_x000D_
Solving the challenge of List Dates Matching Product Rule with Python in Excel
_x000D_
Python in Excel solution 1 for List Dates Matching Product Rule, proposed by Alejandro Campos:
from datetime import datetime, timedelta
def is_valid_date(date):
month = date.month
day = date.day
start_date = datetime(2000, 1, 1)
end_date = datetime(2099, 12, 31)
date_generated = pd.date_range(start=start_date, end=end_date)
valid_dates = [date for date in date_generated if is_valid_date(date)]
result_df = pd.DataFrame(valid_dates, columns=["Valid Dates"])
result_df["Valid Dates"] = result_df["Valid Dates"].dt.strftime("%Y-%m-%d")
result_df
_x000D_
Solving the challenge of List Dates Matching Product Rule with SQL
_x000D_
SQL solution 1 for List Dates Matching Product Rule, proposed by Zoran Milokanović:
WITH /* Microsoft SQL Server 2019 */
CALC
AS
(
SELECT CAST('2001-01-01' AS DATE) AS DATES
UNION ALL
SELECT DATEADD(DAY, 1, C.DATES)
FROM CALC C
WHERE
C.DATES < '2099-12-31'
)
SELECT
C.DATES
FROM CALC C
WHERE
MONTH(C.DATES) * DAY(C.DATES) = YEAR(C.DATES) % 100
OPTION (MAXRECURSION 0)
;
_x000D_
&&