If tasks’ From Time and To Time overlap with each other, mark them as Y. Ex. A’s From and To Times are 01:30 AM to 04:00 AM and B’s From and To Times are 02:00 AM to 05:30PM. There is overlap of times (02:00 AM to 04:00 AM) between A and B. Similarly, D’s To Time is 06:30 PM which overlaps with E’s Start Time of 06:30 PM. A task can not overlap with itself.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 467
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Mark Overlapping Time Ranges with Power Query
Power Query solution 1 for Mark Overlapping Time Ranges, proposed by John V.:
let
S = Excel.CurrentWorkbook(){0}[Content],
C = S[Task ID], A = Table.ToRows(S),
L = List.TransformMany(A, each A, (x, y) => if y{2} < x{1} or y{1} > x{2} or x{0} = y{0} then null else "Y")
in
Table.FromColumns({C} & List.Split(L, 7), {" "} & C)
Blessings!
Power Query solution 2 for Mark Overlapping Time Ranges, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
R = Table.ToRows(Source),
S = Table.FromRows(
List.TransformMany(
R,
each {R},
(i, _) => {i{0}}
& List.Transform(
_,
each {null, "Y"}{Number.From(List.AllTrue({_{0} <> i{0}, i{2} >= _{1}, _{2} >= i{1}}))}
)
),
{" "} & Source[Task ID]
)
in
SPower Query solution 3 for Mark Overlapping Time Ranges, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
List = Table.AddColumn(
Source,
"L",
each Table.SelectRows(
Source,
(f) => (f[To Time] >= [From Time] and f[From Time] <= [To Time]) and f[Task ID] <> [Task ID]
)[Task ID]
),
Expand = Table.ExpandListColumn(List, "L"),
Remove = Table.RemoveColumns(Expand, {"To Time"}),
Return = Table.Pivot(
Remove,
List.Sort(List.Distinct(Remove[L])),
"L",
"From Time",
each if List.Count(_) > 0 then "Y" else null
)
in
ReturnPower Query solution 4 for Mark Overlapping Time Ranges, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Tabla = List.Accumulate(
Source[Task ID],
Source,
(s, c) =>
Table.AddColumn(
s,
c,
(x) =>
let
a =
if x[Task ID] = c then
null
else
let
b = Table.SelectRows(Source, each [Task ID] = c),
d =
if b[To Time]{0} < x[From Time] or b[From Time]{0} > x[To Time] then
null
else
"Y"
in
d
in
a
)
),
Sol = Table.RemoveColumns(Tabla, {"From Time", "To Time"})
in
SolPower Query solution 5 for Mark Overlapping Time Ranges, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.TransformColumns(
S,
{
{
"From Time",
each if Text.Length(Text.BeforeDelimiter(_, ":")) = 1 then Text.Insert(_, 0, "0") else _
},
{
"To Time",
each if Text.Length(Text.BeforeDelimiter(_, ":")) = 1 then Text.Insert(_, 0, "0") else _
}
}
),
b = Table.TransformColumns(
a,
{
{"From Time", each Text.RemoveRange(Text.Remove(_, "."), 7, 1)},
{"To Time", each Text.RemoveRange(Text.Remove(_, "."), 7, 1)}
}
),
c = Table.TransformColumnTypes(b, {{"From Time", type time}, {"To Time", type time}}),
d = List.TransformMany(Table.ToRows(c), (x) => Table.ToRows(c), (x, y) => {x, y}),
e = List.Select(List.Transform(d, each List.Distinct(_)), each List.Count(_) = 2),
f = List.Transform(
List.Transform(e, each List.Combine(_)),
each
if _{4}
>= _{1} and _{4}
<= _{2} or _{5}
>= _{1} and _{5}
<= _{2} or _{1}
>= _{4} and _{1}
<= _{5} or _{2}
>= _{4} and _{2}
<= _{5}
then
List.InsertRange(_, 0, {_{0} & _{3} & "Y"})
else
List.InsertRange(_, 0, {_{0} & _{3} & "N"})
),
g = Table.SplitColumn(
Table.FromColumns({List.Transform(f, each _{0})}),
"Column1",
Splitter.SplitTextByRepeatedLengths(1),
{"C1", "C2", "C3"}
),
Sol = Table.ReorderColumns(
Table.Pivot(
Table.ReplaceValue(g, "N", null, Replacer.ReplaceValue, {"C3"}),
List.Distinct(g[C2]),
"C2",
"C3"
),
c[Task ID]
)
in
SolPower Query solution 6 for Mark Overlapping Time Ranges, proposed by Rafael González B.:
let
Source = Table.TransformColumnTypes(
Excel.CurrentWorkbook(){0}[Content],
{{"From Time", type datetime}, {"To Time", type datetime}}),
TAC = Table.AddColumn,
Laps = TAC(Source, "TimeRanges", each
let
a = Duration.TotalHours([To Time] - [From Time]) * 4 + 1,
b = List.Times([From Time], a, hashtag#duration(0,0,15,0)),
c = List.RemoveLastN(List.RemoveFirstN(b))
in
c)[[Task ID],[TimeRanges]],
AddBase = TAC(Laps, "Base", each 1),
Comb = Table.NestedJoin(AddBase, {"Base"}, AddBase, {"Base"}, "AddBase", 1),
Exp = Table.ExpandTableColumn(Comb, "AddBase",
{"Task ID", "TimeRanges"}, {"Task ID.1", "TimeRanges.1"}),
Power Query solution 7 for Mark Overlapping Time Ranges, proposed by Venkata Rajesh:
let
Source = Data,
Record = Table.AddColumn(
Source,
"Record",
each [
x = Table.SelectRows(
Source,
(r) => [Task ID] <> r[Task ID] and [From Time] <= r[To Time] and [To Time] >= r[From Time]
)[Task ID],
y = List.Repeat({"Y"}, List.Count(x)),
z = Record.FromList(y, x)
][z]
)[[Task ID], [Record]],
Expand = Table.ExpandRecordColumn(Record, "Record", Source[Task ID])
in
ExpandSolving the challenge of Mark Overlapping Time Ranges with Excel
Excel solution 1 for Mark Overlapping Time Ranges, proposed by Bo Rydobon 🇹🇭:
=LET(a,
A2:A8,
b,
B2:B8,
c,
C2:C8,
HSTACK(VSTACK(
"",
a
),
VSTACK(TOROW(
a
),
TRIM(MID(MAP(a,
b,
c,
LAMBDA(i,
f,
t,
CONCAT(IF((a<>i)*(t>=b)*(f<=c),
"Y",
" ")))),
SEQUENCE(
,
ROWS(
a
)
),
1)))))Excel solution 2 for Mark Overlapping Time Ranges, proposed by John V.:
=LET(i,
A2:A8,
b,
B2:B8,
c,
C2:C8,
r,
TOROW,
h,
HSTACK,
VSTACK(h(
"",
r(
i
)
),
h(i,
IF((cr(
c
))+(i=r(
i
)),
"",
"Y")))) Excel solution 3 for Mark Overlapping Time Ranges, proposed by محمد حلمي:
=LET(a,
A2:A8,
b,
B2:B8,
c,
C2:C8,
VSTACK(HSTACK(
"",
TOROW(
a
)
),
HSTACK(a,
IF(MUNIT(
ROWS(
b
)
),
"",
REPT("Y",
(b<=TOROW(
c
))*(c>=TOROW(
b
)))))))Excel solution 4 for Mark Overlapping Time Ranges, proposed by 🇰🇷 Taeyong Shin:
=LET(i,
A2:A8,
f,
B2:B8,
t,
C2:C8,
a,
TOCOL(IFS((f<=TOROW(
t
))*(t>=TOROW(
f
))*NOT(
MUNIT(
ROWS(
i
)
)
),
i&TOROW(
i
))&"Y",
2),
PIVOTBY(
LEFT(
a
),
MID(
a,
2,
1
),
RIGHT(
a
),
SINGLE,
,
0,
,
0
))
BY GROUPBY
=LET(i,
A2:A8,
f,
B2:B8,
t,
C2:C8,
VSTACK(HSTACK(
"",
TOROW(
i
)
),
GROUPBY(i,
REPT("Y",
(f<=TOROW(
t
))*(t>=TOROW(
f
))*NOT(
MUNIT(
ROWS(
i
)
)
)),
SINGLE,
,
0)))Excel solution 5 for Mark Overlapping Time Ranges, proposed by Kris Jaganah:
=LET(a,
A2:A8,
b,
B2:B8*96,
c,
C2:C8*96,
d,
MAP(b,
c,
LAMBDA(y,
z,
ARRAYTOTEXT(FILTER(a,
(by),
0)))),
e,
TOROW(
a
),
f,
IFERROR(
IF(
FIND(
e,
d
),
"Y"
),
""
),
VSTACK(
HSTACK(
"",
e
),
HSTACK(
a,
f&TRANSPOSE(
f
)
)
)) Excel solution 6 for Mark Overlapping Time Ranges, proposed by Julian Poeltl:
=LET(
T,
A2:C8,
P,
TAKE(
T,
,
1
),
TP,
TRANSPOSE(
P
),
F,
CHOOSECOLS(
T,
2
),
To,
TAKE(
T,
,
-1
),
R,
IF(
MAP(
XLOOKUP(
P,
P,
To
)>=XLOOKUP(
TP,
P,
F
),
XLOOKUP(
P,
P,
F
)<=XLOOKUP(
TP,
P,
To
),
TP<>P,
LAMBDA(
A,
B,
C,
AND(
A,
B,
C
)
)
),
"Y",
""
),
VSTACK(
HSTACK(
"",
TP
),
HSTACK(
P,
R
)
)
)Excel solution 7 for Mark Overlapping Time Ranges, proposed by Timothée BLIOT:
=LET(V,
VSTACK,
HSTACK(V(
"",
A2:A8
),
V(TRANSPOSE(
A2:A8
),
IF(DROP(
REDUCE(
"",
ROW(
1:7
),
LAMBDA(
a,
r,
LET(
I,
INDEX,
B,
B2:B8,
C,
C2:C8,
D,
I(
B,
r,
1
),
E,
I(
C,
r,
1
),
V(
a,
--TOROW(
MAP(
B,
C,
LAMBDA(
x,
y,
AND(
x<=E,
y>=D
)
)
)
)
)
)
)
),
1
)*(1-MUNIT(
7
)),
"Y",
""))))Excel solution 8 for Mark Overlapping Time Ranges, proposed by Oscar Mendez Roca Farell:
=LET(a,
A2:A8,
f,
B2:B8,
t,
C2:C8,
v,
TOROW(
f
),
m,
(t>=v)*((fExcel solution 9 for Mark Overlapping Time Ranges, proposed by Sunny Baggu:
=LET(
i,
A2:A8,
f,
B2:B8,
t,
C2:C8,
v,
IF(
MAKEARRAY(
ROWS(
i
),
ROWS(
i
),
LAMBDA(r,
c,
INDEX((INDEX(
f,
r,
1
) <= t) * (INDEX(
t,
r,
1
) >= f) * (r <> c),
c)
)
),
"Y",
""
),
VSTACK(
HSTACK(
"",
TOROW(
i
)
),
HSTACK(
i,
v
)
)
)Excel solution 10 for Mark Overlapping Time Ranges, proposed by LEONARD OCHEA 🇷🇴:
=LET(x,
A2:A8,
i,
B2:B8,
j,
C2:C8,
t,
TOROW(
x
),
VSTACK(HSTACK(
"",
t
),
HSTACK(x,
IF((j>=TOROW(
i
))*(TOROW(
j
)>=i)*(x<>t),
"Y",
""))))
=LET(x,
A2:A8,
t,
TOROW(
x
),
p,
C2:C8>=TOROW(
B2:B8
),
VSTACK(HSTACK(
"",
t
),
HSTACK(x,
IF(p*TRANSPOSE(
p
)*(x<>t),
"Y",
""))))Excel solution 11 for Mark Overlapping Time Ranges, proposed by Andy Heybruch:
=LET(
_task,
A2:A8,
_from,
B2:B8,
_to,
C2:C8,
_ans,
IF(
(_task<>TOROW(
_task
))*
(_from<=TOROW(
_to
))*
(_to>=TOROW(
_from
)),
"Y",
""),
HSTACK(
VSTACK(
"",
_task
),
VSTACK(
TOROW(
_task
),
_ans
)
))Excel solution 12 for Mark Overlapping Time Ranges, proposed by Sandeep Marwal:
=LET(
a,
B2:C8,
b,
A2:A8,
c,
HSTACK(
"",
TRANSPOSE(
b
)
),
VSTACK(
c,
HSTACK(
b,
MAKEARRAY(
7,
7,
LAMBDA(
r,
c,
IFS(
r=c,
"",
TRUE,
IF(
OR(
AND(
INDEX(
a,
r,
1
)INDEX(
a,
c,
2
),
INDEX(
a,
r,
2
)>INDEX(
a,
c,
2
)
)
),
"",
"Y"
)
)
)
)
)
)
) Excel solution 13 for Mark Overlapping Time Ranges, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=IF((((C2:C8-TOROW(B2:B8))*(TOROW(C2:C8)-B2:B8))>0)*(1-MUNIT(ROWS(A2:A8))),"Y","")Excel solution 14 for Mark Overlapping Time Ranges, proposed by Tyler Cameron:
=LET(a,
B2:C8*1440,
b,
A2:A8,
c,
MAKEARRAY(7,
7,
LAMBDA(r,
c,
LET(t,
INDEX(
a,
r,
{1,
2}
),
u,
TOCOL(
INDEX(
a,
c,
{1,
2}
)
),
b,
IF(SUM(--(t=u))>0,
1,
SUM(--(t>u))),
IF(
r=c,
"",
IF(
AND(
b>0,
b<4
),
"Y",
""
)
)))),
HSTACK(
VSTACK(
{""},
b
),
VSTACK(
TOROW(
b
),
c
)
))Excel solution 15 for Mark Overlapping Time Ranges, proposed by Caroline Blake:
=LET(a,
A2:C8,
c,
LAMBDA(
x,
CHOOSECOLS(
a,
x
)
),
r,
LAMBDA(
x,
CHOOSEROWS(
TRANSPOSE(
a
),
x
)
),
HSTACK(VSTACK(
"",
c(
1
)
),
VSTACK(r(
1
),
IF(((r(
2
)>=c(
2
))+(r(
3
)>=c(
2
)))*((r(
2
)<=c(
3
))+(r(
3
)<=c(
3
)))*(c(
2
)<>r(
2
)),
"Y",
""))))Excel solution 16 for Mark Overlapping Time Ranges, proposed by Caroline Blake:
=LET(a,
B2:B8,
_a1,
C2:C8,
b,
TRANSPOSE(
a
),
c,
TRANSPOSE(
_a1
),
r,
IF(ISERROR(FIND("TRUE",
(b>=a)&(c>=a))),
"",
"Y"),
_r1,
IF(ISERROR(FIND("TRUE",
(b<=_a1)&(c<=_a1))),
"",
"Y"),
_r3,
IF(
r="Y",
IF(
_r1="Y",
IF(
a=b,
"",
"Y"
),
""
),
""
),
HSTACK(
VSTACK(
"",
A2:A8
),
VSTACK(
TRANSPOSE(
A2:A8
),
_r3
)
))Excel solution 17 for Mark Overlapping Time Ranges, proposed by Will Freestone:
=LET(a,
--(B2:B8<=TOROW(
C2:C8
)),
t,
A2:A8,
b,
a*TRANSPOSE(
a
),
VSTACK(
HSTACK(
"",
TOROW(
t
)
),
HSTACK(
t,
IF(
MAKEARRAY(
7,
7,
LAMBDA(
r,
c,
IF(
r=c,
0,
INDEX(
b,
r,
c
)
)
)
),
"Y",
""
)
)
))Excel solution 18 for Mark Overlapping Time Ranges, proposed by Will Freestone:
=LET(a,
--(B2:B8<=TOROW(
C2:C8
)),
t,
A2:A8,
b,
a*TRANSPOSE(
a
),
VSTACK(HSTACK(
"",
TOROW(
t
)
),
HSTACK(t,
IF(b*(1-MUNIT(
7
)),
"Y",
""))))Solving the challenge of Mark Overlapping Time Ranges with Python
Python solution 1 for Mark Overlapping Time Ranges, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
input = pd.read_excel("467 Overlapping Times.xlsx", usecols="A:C", nrows=7)
test = pd.read_excel("467 Overlapping Times.xlsx", usecols="E:L", nrows=8, skiprows=1)
test.columns = ['Task ID'] + list(test.columns[1:])
test = test.set_index('Task ID')
input['key'] = 0
input = input.merge(input, on='key').drop('key', axis=1)
input = input[input['Task ID_x'] != input['Task ID_y']]
input['overlap'] = (input['To Time_x'] >= input['From Time_y']) & (input['From Time_x'] <= input['To Time_y'])
input['overlap'] = input['overlap'].map({True: 'Y', False: np.nan})
input = input[['Task ID_x', 'Task ID_y', 'overlap']]
input = input.pivot(index='Task ID_x', columns='Task ID_y', values='overlap')
input.reset_index(drop=True, inplace=True)
input.fillna('', inplace=True)
test.reset_index(drop=True, inplace=True)
test.fillna('', inplace=True)
test.columns = input.columns
print(input.equals(test))
Solving the challenge of Mark Overlapping Time Ranges with Python in Excel
Python in Excel solution 1 for Mark Overlapping Time Ranges, proposed by Abdallah Ally:
import pandas as pd
from datetime import datetime, time, timedelta
# Function to generate time intervals
def time_intervals(start_time, end_time, interval=15):
start_date = datetime(1900, 1, 1) # Arbitrary date
start_time = datetime.combine(start_date, start_time)
end_time = datetime.combine(start_date, end_time)
times = []
current_datetime = start_time
while current_datetime <= end_time:
times.append(current_datetime.time())
current_datetime += timedelta(minutes=interval)
return times
file_path = 'Excel_Challenge_467 - Overlapping Times.xlsx'
df = pd.read_excel(file_path, usecols='A:C', nrows=7)
# Perform data wrangling
df = df.set_index('Task ID', drop=True)
items = []
for i in df.index:
item = [i]
for j in df.index:
a, b = df.loc[i, 'From Time':], df.loc[j, 'From Time':]
if i == j:
item.append('')
else:
intersect = any([x in time_intervals(*b) for x in time_intervals(*a)])
item.append('Y' if intersect else '')
items.append(item)
df = pd.DataFrame(items, columns=[''] + list(df.index))
df
Solving the challenge of Mark Overlapping Time Ranges with R
R solution 1 for Mark Overlapping Time Ranges, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/467 Overlapping Times.xlsx", range = "A1:C8")
test = read_excel("Excel/467 Overlapping Times.xlsx", range = "E2:L9") %>%
select(`Task ID` = ...1, everything())
r1 = input %>%
mutate(interval = interval(`From Time`, `To Time`)) %>%
select(-`From Time`, -`To Time`)
combinations = expand_grid(r1, r1, .name_repair = "unique") %>%
filter(`Task ID...1` != `Task ID...3`) %>%
mutate(overlap = ifelse(int_overlaps(interval...2, interval...4), "Y", NA_character_)) %>%
select(`Task ID`, sort(colnames(.)))
identical(test, combinations)
# [1] TRUE
R solution 2 for Mark Overlapping Time Ranges, proposed by Anil Kumar Goyal:
library(readxl)
library(dplyr)
library(igraph)
df <- read_excel("Excel/Excel_Challenge_467 - Overlapping Times.xlsx", range = cell_cols(LETTERS[1:3]))
df |>
inner_join(df,
join_by(`Task ID` < `Task ID`,
overlaps(`From Time`, `To Time`, `From Time`, `To Time`))) |>
select(from = `Task ID.x`, to = `Task ID.y`) |>
graph_from_data_frame(directed = FALSE, vertices = LETTERS[1:7]) |>
as_adjacency_matrix() |>
as.matrix() |>
as.data.frame() |>
tibble::rownames_to_column(" ") |>
mutate(across(A:G, ~ifelse(. == 1, "Y", " ")))
&&