Extract the Date, Day of the Week and Time Dynamic array function allowed, but Extra marks for Legacy solutions or PowerQuery Solutions.
📌 Challenge Details and Links
Challenge Number: 68
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Extract Date Day and Time with Power Query
Power Query solution 1 for Extract Date Day and Time, proposed by Kris Jaganah:
let a = Text.Split( Text.Remove(_,{".",","})," ") in
[Date = Date.From( a{1}& Text.Start( a{0},3)& a{2} ) ,
Weekday = Date.DayOfWeekName(Date),
Time = Time.From( a{3} & a{4}) ] })[Dates])Power Query solution 2 for Extract Date Day and Time, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Tbl = Table.Combine(
Table.AddColumn(
Origen,
"A",
each
let
a = [Dates],
b = List.Last(Text.PositionOf(a, ", ", 2)),
c = Splitter.SplitTextByPositions({0, b})(a),
d = List.Transform(c, each Text.TrimStart(_, {",", " "})),
e = try Date.From(d{0}) otherwise Date.From(Text.Replace(d{0}, "Sept", "Sep")),
f = Date.DayOfWeekName(e),
g = Table.FromColumns({{e}, {f}, {Text.Upper(List.Last(d))}}, {"Date", "Day", "Time"})
in
g
)[A]
)
in
TblPower Query solution 3 for Extract Date Day and Time, proposed by Brian Julius:
let
Source = Table,
AISolve = fnPowerSolve_gpt4o(
Table,
Table[Dates],
"Result",
"For each row in Dates column, split the text string into 3 separate values: a Date in the form m/d/yyyy, a Text day name, and a time in hh:mm AM/PM format. Return only the values in the form of a single test string separated by commas in the form Date, Day, Time. Include no other text.",
0
),
RemCol = Table.RemoveColumns(AISolve, {"Dates"}),
Split = Table.SplitColumn(
AISolve,
"Result",
Splitter.SplitTextByDelimiter(","),
{"Date", "Day", "Time"}
)
in
SplitPower Query solution 4 for Extract Date Day and Time, proposed by Peter Krkos:
let a = Text.BeforeDelimiter([Dates], ",", {1, RelativePosition.FromStart})
in Date.FromText(Text.Start(Text.BeforeDelimiter(a, "."), 3) & Text.AfterDelimiter(a, "."), [Format="MMM dd, yyyy", Culture="en-US"]),
Day = Date.DayOfWeekName(Date, "en-US"),
Time = Time.FromText(Text.Remove(Text.AfterDelimiter([Dates], ", ", {0, RelativePosition.FromEnd}), "."), [Culture="en-US"])
])
[R])Solving the challenge of Extract Date Day and Time with Excel
Excel solution 1 for Extract Date Day and Time, proposed by Rick Rothstein:
=TEXT(
SUBSTITUTE(
SUBSTITUTE(
B4:B7,
".",
""),
",",
"",
2),
{"m/d/e",
"dddd",
"h:mm AM/PM"})Excel solution 2 for Extract Date Day and Time, proposed by Kris Jaganah:
=REDUCE(
{"Date",
"Day",
"Time"},
B4:B7,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
SUBSTITUTE(
TEXTSPLIT(
y,
,
{". ",
", "}),
".",
""),
b,
--CONCAT(
INDEX(
a,
{2,
1,
3})),
HSTACK(
TEXT(
b,
"dd/mm/yyyy"),
TEXT(
WEEKDAY(
b),
"dddd"),
TEXT(
--TAKE(
a,
-1),
"H:MM AM/PM"))))))Excel solution 3 for Extract Date Day and Time, proposed by Hussein SATOUR:
=REDUCE(
{"Date",
"Day",
"Time"},
B4:B7,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
--TRIM(
CONCAT(
CHOOSECOLS(
TEXTSPLIT(
y,
{".",
","},
,
1),
2,
1,
3,
4,
5))),
HSTACK(
INT(
a),
TEXT(
a,
"dddd"),
a-INT(
a))))))Excel solution 4 for Extract Date Day and Time, proposed by Sunny Baggu:
=HSTACK(
TEXT(
SUBSTITUTE(
TEXTBEFORE(
B4:B7,
", ",
2),
".",
""),
{"m/d/yyy",
"dddd"}
),
UPPER(
TEXTAFTER(
B4:B7,
", ",
-1))
)Excel solution 5 for Extract Date Day and Time, proposed by Pieter de B.:
=LET(
b,
SUBSTITUTE(
B4:B7,
".",
),
d,
TEXTBEFORE(
b,
",",
2),
HSTACK(
--d,
TEXT(
d,
"dddd"),
UPPER(
TEXTAFTER(
b,
", ",
2))))Excel solution 6 for Extract Date Day and Time, proposed by Hamidi Hamid:
=LET(
tp,
TEXTAFTER,
b,
B4:B7,
f,
LAMBDA(
v,
TEXTBEFORE(
b,
v,
)),
m,
f(
"."),
mm,
PROPER(
TEXT(
"1/"&SEQUENCE(
12),
"mmm")),
j,
tp(
f(
","),
".")*1&"/"&XLOOKUP(
m,
mm,
mm,
,
3)&"/"&TEXTBEFORE(
tp(
b,
", ",
),
", ")*1,
jj,
TEXT(
j,
"dddd"),
t,
UPPER(
SUBSTITUTE(
tp(
b,
", ",
-1),
".",
)),
HSTACK(
j,
jj,
t))Excel solution 7 for Extract Date Day and Time, proposed by Meganathan Elumalai:
=REDUCE(
D3:F3,
B4:B7,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
SORTBY(
SUBSTITUTE(
TEXTSPLIT(
y,
{". ",
", "}),
".",
),
{2,
1,
3,
4}),
b,
TEXTJOIN(
"-",
,
TAKE(
a,
,
3))&" "&TAKE(
a,
,
-1),
TEXT(
b,
{"dd-mm-e",
"dddd",
"h:mm am/pm"})))))Excel solution 8 for Extract Date Day and Time, proposed by Milan Shrimali:
BYROW(
B4:B7,
LAMBDA(
x,
LET(
a,
x,
mn,
REGEXEXTRACT(
a,
"[A-z]+"),
dy,
REGEXEXTRACT(
a,
"[0-9]+"),
yr,
REGEXEXTRACT(
a,
"d{4}"),
dt,
TEXT(
TEXTJOIN(
"-",
,
dy,
mn,
yr),
"mm/dd/yy"),
tm,
TIME(
TEXTBEFORE(
TRIM(
TEXTAFTER(
a,
",",
-1)),
":"),
TEXTBEFORE(
TEXTAFTER(
TEXTAFTER(
A1,
",",
-1),
":"),
" "),
0),
HSTACK(
dt,
TEXT(
dt,
"dddd"),
tm))))Excel solution 9 for Extract Date Day and Time, proposed by red craven:
=LET(
a,
SUBSTITUTE(
SUBSTITUTE(
B4:B7,
".",
),
",",
,
2),
HSTACK(
INT(
a),
TEXT(
a,
"dddd"),
MOD(
a,
1)))Solving the challenge of Extract Date Day and Time with Python
Python solution 1 for Extract Date Day and Time, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "files/Ex-Challenge 09 2025.xlsx"
input = pd.read_excel(path, usecols="B", skiprows=2, nrows=5)
test = pd.read_excel(path, usecols="D:F", skiprows=2, nrows=5)
input['Dates'] = input['Dates'].apply(lambda x: x.split()[0][:3] + ' ' + ' '.join(x.split()[1:]))
input['Dates'] = input['Dates'].str.replace('.', '', regex=False)
input['Dates'] = input['Dates'].str.replace('am', 'AM').str.replace('pm', 'PM')
input['Date'] = pd.to_datetime(input['Dates']).dt.date.astype('datetime64[ns]')
input['Day'] = pd.to_datetime(input['Dates']).dt.day_name()
input['Time'] = pd.to_datetime(input['Dates']).dt.time
input = input.drop(columns=['Dates'])
print(input.equals(test)) # TrueSolving the challenge of Extract Date Day and Time with Python in Excel
Python in Excel solution 1 for Extract Date Day and Time, proposed by Alejandro Campos:
from datetime import datetime
df = pd.DataFrame([
(d := datetime.strptime(date.replace('Sept.', 'Sep.').replace('a.m.', 'AM').replace(
'p.m.', 'PM').replace('.', ''), '%b %d, %Y, %I:%M %p'),
d.strftime('%d/%m/%Y'), d.strftime('%A'), d.strftime('%I:%M %p'))[1:]
for date in xl("B3:B7", headers=True)['Dates']], columns=['Date', 'Day', 'Time'])Solving the challenge of Extract Date Day and Time with R
R solution 1 for Extract Date Day and Time, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(lubridate)
library(hms)
path = "files/Ex-Challenge 09 2025.xlsx"
input = read_excel(path, range = "B3:B7")
test = read_excel(path, range = "D3:F7") %>%
mutate(Time = as_hms(Time) %>% as.POSIXct())
result = input %>%
mutate(Dates = gsub("\.", "", Dates)) %>%
mutate(Dates = parse_date_time(Dates, "b. d, Y, I:M p")) %>%
mutate(Date = as.Date(Dates) %>% as.POSIXct(),
Day = wday(Dates, label = TRUE, abbr = FALSE, locale = "en") %>% as.character(),
Time = as_hms(Dates) %>% as.POSIXct()) %>%
select(-Dates)
all.equal(result, test, check.attributes = FALSE)
# [1] TRUE