Calculate the Turn Around Time (TAT) i.e. column C. TAT is the difference in hours between A2 and B2. 1. Weekends (Sat & Sun) need to be excluded. 2. Holidays (column H) need to be excluded. 3. Only time between Start Time and End Time given in column F need to be considered. Hence, time before 9 AM and time after 6 PM need to be excluded. Hence, if start time is 12/26/2022 04:00 AM and end time is 12/26/2022 11:00 AM, 04:00 AM to 09:00 AM time will be excluded. And time from 09:00 AM to 11:00 AM will be considered. Hence answer will be 2 hours. Hence, if start time is 12/26/2022 04:00 AM and end time is 12/26/2022 08:00 PM, 04:00 AM to 09:00 AM time will be excluded. And time from 09:00 AM to 06:00 PM will be considered and 06:00PM to 08:00 PM will be discarded. Hence answer will be 9 hours.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 96
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Calculate Turnaround Time with Power Query
Power Query solution 1 for Calculate Turnaround Time, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Hday = List.Transform(
Excel.CurrentWorkbook(){[Name = "Holiday"]}[Content][Holidays List],
Date.From
),
TAT = Table.AddColumn(
Source,
"TAT",
each
let
zd = Duration.From(0),
s = Time.From("9:00"),
e = Time.From("18:00"),
a = Date.From([Start Date Time]),
b = Date.From([End Date Time]),
wd = List.Transform(
List.Dates(a, Duration.Days(b - a) + 1, Duration.From(1)),
each if Date.DayOfWeek(_, 1) < 5 and not List.Contains(Hday, _) then e - s else zd
)
in
List.Sum(
wd
& {
if List.First(wd) > zd then
List.Min({zd, s - List.Min({e, Time.From([Start Date Time])})})
else
zd,
if List.Last(wd) > zd then
List.Min({zd, List.Max({s, Time.From([End Date Time])}) - e})
else
zd
}
)
)
in
TATPower Query solution 2 for Calculate Turnaround Time, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name="TurnAroundTime"]}[Content],
Type = Table.TransformColumnTypes(Source, {{"Start Date Time", type datetime}, {"End Date Time", type datetime}}, "en-US"),
TAT = Table.AddColumn(Type, "TAT", each [
x = List.Skip(List.RemoveLastN({Int64.From(Date.From([Start Date Time]))..Int64.From(Date.From([End Date Time]))}),1),
y = List.RemoveItems(x,List.Transform(Holidays, Int64.From)),
z = List.Count(List.Select(y, each Date.DayOfWeek(Date.From(_))<5))*9,
a = hashtag#time(18,0,0) - List.Max({DateTime.Time([Start Date Time]),hashtag#time(9,0,0)}),
b = if Date.DayOfWeek([Start Date Time]) > 4 or a + hashtag#time(0,0,0) >= hashtag#time(18,0,0) or List.Contains(Holidays,Date.From([Start Date Time])) then hashtag#duration(0,0,0,0) else a,
c = List.Min({DateTime.Time([End Date Time]),hashtag#time(18,0,0)}) - hashtag#time(9,0,0),
d = if Date.DayOfWeek([End Date Time]) > 4 or c + hashtag#time(0,0,0) >= hashtag#time(15,0,0) or List.Contains(Holidays,Date.From([End Date Time])) then hashtag#duration(0,0,0,0) else c,
e = if Date.From([Start Date Time]) <> Date.From([End Date Time]) then b + d +hashtag#duration(0,z,0,0) else List.Min({DateTime.Time([End Date Time]),hashtag#time(18,0,0)}) - List.Max({DateTime.Time([Start Date Time]),hashtag#time(9,0,0)})
][e])[[TAT]]
in
TAT
Power Query solution 3 for Calculate Turnaround Time, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Holidays = List.Transform(Excel.CurrentWorkbook(){[Name="Holidays"]}[Content][Holidays List], Date.From),
TAT = Table.AddColumn(Source, "TAT", each
[
getTimes = {[Start Date Time]} & List.RemoveLastN(List.Skip(List.DateTimes( Time.StartOfHour( [Start Date Time] ), Number.RoundUp(Duration.TotalHours( [End Date Time] - [Start Date Time]),0), hashtag#duration(0,1,0,0))),1) & {[End Date Time]},
filterTimes = List.Select(getTimes, each (Time.Hour(_) >= 9 and Time.Hour(_) <= 18) and Date.DayOfWeek(_, 1) < 5 and not List.Contains(Holidays, Date.From(_))),
getDurations = List.Transform( {1..List.Count(filterTimes)}, each try (if Date.From(filterTimes{_}) = Date.From(filterTimes{_-1}) then Duration.TotalHours(filterTimes{_} - filterTimes{_-1}) else 0) otherwise 0),
getTotal = List.Sum(getDurations),
format = let h = Number.RoundDown(getTotal,0) in if getTotal = null then hashtag#duration(0,0,0,0) else hashtag#duration(0, h, Number.RoundUp((getTotal - h) * 60,2),0),
totext = Duration.ToText(format)
][totext]
)
in
TAT
Solving the challenge of Calculate Turnaround Time with Excel
Excel solution 1 for Calculate Turnaround Time, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
s,
F1,
e,
F2,
h,
H2:H5,
SUM(
NETWORKDAYS(
HSTACK(
a,
a,
b
),
HSTACK(
b,
a,
b
),
h
)*HSTACK(
e-s,
MIN(
0,
s-MIN(
e,
MOD(
a,
1
)
)
),
MIN(
0,
MAX(
s,
MOD(
b,
1
)
)-e
)
)
)
)
)
)Excel solution 2 for Calculate Turnaround Time, proposed by John V.:
=MAP(A2:A10,
B2:B10,
LAMBDA(x,
y,
LET(h,
H2:H5,
f,
LAMBDA(t,
s,
a,
IF(OR(SUM(--(h=INT(
a
))),
WEEKDAY(
a,
2
)>5),
,
s*(MEDIAN(
F1,
F2,
MOD(
a,
1
)
)-t))),
NETWORKDAYS.INTL(
x,
y,
,
h
)*(F2-F1)-f(
F1,
1,
x
)-f(
F2,
-1,
y
))))Excel solution 3 for Calculate Turnaround Time, proposed by محمد حلمي:
=MAP(A2:A10,B2:B10,LAMBDA(a,b,LET(c,INT(a),
e,SEQUENCE(INT(b)-c+1,,c),i,IF(e=MAX(e),
IF(MOD(b,1)<=F1,-e,MIN(MOD(b,1),F2)),F2)+e,j,IF(e=@e,
IF(MOD(a,1)>=F2,-@e,MAX(MOD(a,1),F1)),F1)+e,
SUM(IF(i*j*NETWORKDAYS(j,j,H2:H5),i-j)))))Excel solution 4 for Calculate Turnaround Time, proposed by Kris Jaganah:
=LET(
a, A2:A10,
b, B2:B10,
c, F1,
d, F2,
e, H2:H5,
f, DATE(YEAR(a), MONTH(a), DAY(a)),
g, MAP(a, f, LAMBDA(x,y, MAX(x, y + c))),
h, g - f,
i, DATE(YEAR(b), MONTH(b), DAY(b)),
j, MAP(b, i, LAMBDA(x,y, MIN(x, y + d))),
k, j - i,
l, i - f + 1,
m, SEQUENCE(ROWS(a)),
n, DATE(YEAR(TAKE(e, 1)), MONTH(1), SEQUENCE(365)),
o, IF(XLOOKUP(n, e, e, 0, 0, 1) > 1, 0, 1) *
IF(WEEKDAY((n), 2) > 5, 0, 1),
p, XLOOKUP(n, f, h, 0, 0, 1),
q, XLOOKUP(n, i, k, 0, 0, 1),
r, BYROW(
n,
LAMBDA(x, SUMPRODUCT(--(x >= f), --(x <= i), m))
),
s, MAP(
r,
p,
q,
LAMBDA(x,y,z,
IF(
x = 0,
0,
IF(
AND(y > 0, y < d),
y,
IF(AND(z >= 0, y = 0), c, IF(y >= d, d))
)
)
)
),
t, MAP(
r,
p,
q,
s,
LAMBDA(w,x,y,z,
IF(
w = 0,
0,
IF(
IF(AND(x >= 0, y > 0), y, d) < z,
z,
IF(AND(x >= 0, y > 0), y, d)
)
)
)
),
u, (t - s) * o,
v, BYROW(m, LAMBDA(x, SUM(IF(r = x, u, 0)))),
v
)Excel solution 5 for Calculate Turnaround Time, proposed by Julian Poeltl:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
S,
E,
LET(
SQ,
ROUNDDOWN(
SEQUENCE(
ROUNDDOWN(
E,
0
)-ROUNDDOWN(
S,
0
)+1,
,
S
),
0
),
MS,
MAX(
S,
TAKE(
SQ,
1
)+0,
375
),
ET,
MIN(
E,
TAKE(
SQ,
-1
)+0,
75
),
D,
MAP(
SQ,
LAMBDA(
A,
NETWORKDAYS(
A,
A,
H2:H5
)
)
),
M,
IFERROR(
SUM(
DROP(
DROP(
D*9,
1
),
-1
)
),
0
),
FC,
TAKE(
SQ,
1
)+0,
75-MS,
F,
TAKE(
D,
1
)*IF(
FC>0,
FC,
0
),
LC,
ET-TAKE(
SQ,
-1
)-0,
375,
L,
TAKE(
D,
-1
)*IF(
LC>0,
LC,
0
),
R,
F+M/24+L,
IF(
ROWS(
SQ
)=1,
R-9/24,
R
)
)
)
)Excel solution 6 for Calculate Turnaround Time, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_s, A2:A10,
_e, B2:B10,
_st, G1,
_et, G2,
_h, I2:I5,
_exp, LAMBDA(a, b,
(NETWORKDAYS(a, b, _h) - 1) * (_et - _st) +
IF(NETWORKDAYS(b, b, _h), MEDIAN(MOD(b, 1), _et, _st), _et) -
MEDIAN(NETWORKDAYS(a, a, _h) * MOD(a, 1), _et, _st)
),
MAP(_s, _e, _exp)
)Excel solution 7 for Calculate Turnaround Time, proposed by Timothée BLIOT:
=LET(A, $A$2:$A$10, B, $B$2:$B$10, SH,$F$1, EH, $F$2, H, $H$2:$H$5,
D, LAMBDA(x, DATE(YEAR(x),MONTH(x),DAY(x))),
S, LAMBDA(a,b, SEQUENCE(D(b)-D(a)+1,,D(a))),
SequD, LAMBDA(a,b, IFERROR(FILTER(S(a,b), (MAP(S(a,b), LAMBDA(x, --NOT(SUM(--(x=H))) )))*WEEKDAY(S(a,b),16)>2 ),"") ),
Start, LAMBDA(a,b, IF(SequD(a,b)="",0, MAP(SequD(a,b), LAMBDA(x, IF(x=MIN(S(a,b)),MIN(MAX(a-D(a),SH),EH),SH) ) ))),
End, LAMBDA(a,b, IF(SequD(a,b)="",0, MAP(SequD(a,b), LAMBDA(x, IF(x=MAX(S(a,b)),MAX(MIN(b-D(b),EH),SH),EH) ) ) )),
MAP(A,B, LAMBDA(a,b, SUM(End(a,b)-Start(a,b)) )) )Excel solution 8 for Calculate Turnaround Time, proposed by Stefan Olsson:
=LAMBDA(a,b,_s,_e,h,
MAP(a,b,
LAMBDA(s, e,
IFS(
INT(s)=INT(e),
MIN(_e, MOD(e,1))-MAX(_s, MOD(s, 1))*NETWORKDAYS(s, e, h),
TRUE,
SUM(
NETWORKDAYS(s, s, h)*IF(MOD(s, 1)>_e, 0, SUM(_e,-MAX(0, _s, MOD(s, 1)))),
MAX(0, NETWORKDAYS(s+1, e-1,h))*(_e-_s),
NETWORKDAYS(e, e, h)*IF(MOD(e, 1)<_s, 0, SUM(-_s,MIN(_e, MOD(e, 1))))
)
)
)
)
)(A2:A10,B2:B10,F1,F2,H2:H5)Excel solution 9 for Calculate Turnaround Time, proposed by Guillermo Arroyo:
= BYROW(
A2:B10,
LAMBDA(
x,
LET(
_List,
SEQUENCE((INDEX(x, , 2) - INDEX(x, , 1)) * 24 * 60, , INDEX(x, , 1), 1 / 1440),
_Hour,
ROUND(_List - TRUNC(_List), 6),
_newList,
FILTER(
_List,
(WEEKDAY(_List, 2) < 6) * (COUNTIF(H2:H5, TRUNC(_List)) = 0) * (_Hour >= F1) * (_Hour < F2),
""),
IF(AND(_newList = "", ROWS(_newList) = 1), 0, ROWS(_newList)) / 24 / 60)))Solving the challenge of Calculate Turnaround Time with Python
Solving the challenge of Calculate Turnaround Time with SQL
SQL solution 1 for Calculate Turnaround Time, proposed by Zoran Milokanović:
1/3
WITH -- Microsoft SQL Server 2019
DATA_PREPARATION_TIMES
AS
(
SELECT
T.START_DATE_TIME AS START_DATE_TIME_ORIG
,CAST(T.START_DATE_TIME AS DATETIME) AS START_DATE_TIME
,CAST(T.START_DATE_TIME AS DATE) AS START_DATE
,T.END_DATE_TIME AS END_DATE_TIME_ORIG
,CAST(T.END_DATE_TIME AS DATETIME) AS END_DATE_TIME
,CAST(T.END_DATE_TIME AS DATE) AS END_DATE
FROM TIMES T
),
DATA_PREPARATION_HOLIDAYS
AS
(
SELECT
CAST(HL.HOLIDAY AS DATE) AS HOLIDAY
FROM HOLIDAYS_LIST HL
),
DATES
AS
(
SELECT
MIN(DPT.START_DATE) AS START_DATE
,MAX(DPT.END_DATE) AS END_DATE
FROM DATA_PREPARATION_TIMES DPT
UNION ALL
SELECT
DATEADD(DAY, 1, DPT.START_DATE) AS START_DATE
,DPT.END_DATE
FROM DATES DPT
WHERE
DPT.START_DATE < DPT.END_DATE
),
INTERSECTION
AS
(
SELECT
DPT.START_DATE_TIME_ORIG
,DPT.START_DATE_TIME
,DPT.START_DATE
,DPT.END_DATE_TIME_ORIG
,DPT.END_DATE
,T.START_TIME
,T.END_TIME