Find the total time in hours worked on each day. Time is tabulated in hhmm format (24 hours format). So for Thomas on Sun, time worked = 15:00 – 12:00 = 3 Hours
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 639
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Calculate Daily Work Hours with Power Query
Power Query solution 1 for Calculate Daily Work Hours, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.UnpivotOtherColumns(A, {"Name"}, "Day", "V"),
C = Table.TransformColumns(
B,
{"V", each [a = (v) => Number.From(Time.From(Text.Split(_, "-"){v})) * 24, b = a(1) - a(0)][b]}
),
D = Table.Group(C, "Day", {"Sum", each List.Sum([V])})
in
DPower Query solution 2 for Calculate Daily Work Hours, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Name"}, "Day", "Hours"),
Custom = Table.AddColumn(
Unpivot,
"Custom",
each
let
a = Time.From(Text.End([Hours], 4)) - Time.From(Text.Start([Hours], 4)),
b = Duration.Hours(a) + Duration.Minutes(a) / 60
in
b
),
Final = Table.Group(Custom, {"Day"}, {{"Total Hours", each List.Sum([Custom])}})
in
FinalPower Query solution 3 for Calculate Daily Work Hours, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
RCol = Table.RemoveColumns(Origen, {"Name"}),
DH = Table.DemoteHeaders(RCol),
Horas = Table.AddColumn(
Table.Transpose(DH),
"A",
each
let
a = List.RemoveNulls(List.Skip(Record.ToList(_))),
b = List.Transform(
a,
each List.Transform(
Text.Split(_, "-"),
(x) => Number.From(Text.Start(x, 2)) + Number.From(Text.End(x, 2)) / 60
)
),
c = List.Sum(List.Transform(b, each _{1} - _{0}))
in
c
)[A],
Sol = Table.FromColumns({List.Skip(Table.ColumnNames(Origen)), Horas}, {"Day", "Total Hours"})
in
SolPower Query solution 4 for Calculate Daily Work Hours, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = Table.TransformColumns(
Table.RemoveColumns(Source, {"Name"}),
{},
each Duration.TotalHours(
Duration.FromText(Text.Insert(Text.End(_, 4), 2, ":"))
- Duration.FromText(Text.Insert(Text.Start(_, 4), 2, ":"))
)
),
Result = Table.FromRows(
List.Transform(Table.ColumnNames(Transform), (f) => {f, List.Sum(Table.Column(Transform, f))}),
{"Day", "Total Hours"}
)
in
ResultPower Query solution 5 for Calculate Daily Work Hours, proposed by Rafael González B.:
let
Source = Question_Table,
TC = List.Skip(Table.ToColumns(Source)),
LT = List.Transform(TC, each List.Sum(
List.Transform(List.RemoveNulls(_),
(x) => Expression.Evaluate(x) * -0.01)
)
),
Result = Table.FromColumns({List.Skip(Table.ColumnNames(Source)) , LT},
{"Day", "Total Hours"})
in
Result
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Power Query solution 6 for Calculate Daily Work Hours, proposed by Krzysztof Kominiak:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"XU67CsMwDPwXzQlIdus0Y9aMIZvJkIIhS21I+/9EtmIsChqOe+jOe1iP9Nm/0AEZxJ6eiIxxyNjagl3GD8WbgomUf2TMdPMwAVvnYYoxVNJIUJ88v/nsn9MRRZI9Q41wG3+w+oP4d2mQOa5NaG3EsSr9q/nHkt7h/GkRVQ2+2o6yydAd3S4=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Name = _t, Sun = _t, Mon = _t, Tue = _t, Wed = _t, Thu = _t, Fri = _t, Sat = _t]
),
Transform = Table.TransformColumns(
Source,
{{"Name", each _}},
each [
a = List.Transform(Text.Split(_, "-"), (x) => Time.From(x)),
b = try Duration.TotalHours(a{1} - a{0}) otherwise null
][b]
),
UnpivotOthCols = Table.UnpivotOtherColumns(Transform, {"Name"}, "Attr", "Value"),
Result = Table.Group(
UnpivotOthCols,
{"Attr"},
{{"Total Hours", each List.Sum([Value]), type number}}
)
in
ResultPower Query solution 7 for Calculate Daily Work Hours, proposed by Krupesh Bhansali:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Day", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(
#"Unpivoted Other Columns",
"Value",
Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv),
{"Value.1", "Value.2"}
),
#"Changed Type" = Table.TransformColumnTypes(
#"Split Column by Delimiter",
{{"Value.2", type time}, {"Value.1", type time}}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Hours Worked",
each [Value.2] - [Value.1],
type number
),
#"Calculated Total Hours" = Table.TransformColumns(
#"Added Custom",
{{"Hours Worked", Duration.TotalHours, type number}}
),
#"Grouped Rows" = Table.Group(
#"Calculated Total Hours",
{"Day"},
{{"Sum", each List.Sum([Hours Worked]), type number}}
)
in
#"Grouped Rows"Solving the challenge of Calculate Daily Work Hours with Excel
Excel solution 1 for Calculate Daily Work Hours, proposed by Rick Rothstein:
=LET(f,
LAMBDA(c,
x,
y,
SUM(24*(IFERROR({1,
-1}*(MID(
c,
x+{5,
0},
2
)&":"&MID(
c,
y+{5,
0},
2
)),
0)))),
TRANSPOSE(
VSTACK(
B1:H1,
BYCOL(
B2:H6,
LAMBDA(
t,
f(
t,
1,
3
)
)
)
)
))Excel solution 2 for Calculate Daily Work Hours, proposed by John V.:
=TRANSPOSE(
VSTACK(
B1:H1,
BYCOL(
B2:H6,
LAMBDA(
x,
SUM(
TEXT(
0&MID(
x,
{6,
1},
4
),
"0:00"
)*{24,
-24}
)
)
)
)
)Excel solution 3 for Calculate Daily Work Hours, proposed by Kris Jaganah:
=VSTACK({"Day","Total Hours"},TRANSPOSE(VSTACK(B1:H1,BYCOL(B2:H6,LAMBDA(x,SUM(MMULT(CEILING(TEXTSPLIT(TEXTJOIN(",",,x),"-",",")/100,0.5),{-1;1})))))))Excel solution 4 for Calculate Daily Work Hours, proposed by Timothée BLIOT:
=LET(A,
GROUPBY(TOCOL(
IF(
B2:H6=B1:H1,
,
B1:H1
),
,
1
),
IFERROR(MAP(TOCOL(
B2:H6,
,
1
),
LAMBDA(x,
--MID(
x,
6,
2
)---LEFT(
x,
2
)+((--RIGHT(
x,
2
)---MID(
x,
3,
2
))/60) )),
0),
SUM,
,
0),
SORTBY(
A,
XMATCH(
TAKE(
A,
,
1
),
TEXT(
ROW(
1:7
),
"DDD"
)
)
))Excel solution 5 for Calculate Daily Work Hours, proposed by Hussein SATOUR:
=TRANSPOSE(VSTACK(B1:H1,
BYCOL(B2:H6,
LAMBDA(x,
LET(W,
ROUNDDOWN,
l,
LEFT(
x,
4
)/100,
r,
RIGHT(
x,
4
)/100,
h,
W(
r,
0
)-W(
l,
0
),
ml,
(l-W(
l,
0
))*5/3,
mr,
(r-W(
r,
0
))*5/3,
mc,
mr-ml,
SUM(
IFERROR(
h+mc,
0
)
))))))Excel solution 6 for Calculate Daily Work Hours, proposed by Oscar Mendez Roca Farell:
=LET(d,B2:H6,F,LAMBDA(i,MID(d,i,2)),HSTACK(TOCOL(B1:H1),TOCOL(BYCOL(IFERROR(F(6)+F(8)/60-F(1)-F(3)/60,),SUM))))Excel solution 7 for Calculate Daily Work Hours, proposed by Duy Tùng:
=TRANSPOSE(
VSTACK(
B1:H1,
BYCOL(
IFERROR(
MAP(
B2:H6,
LAMBDA(
x,
BYROW(
REPLACE(
MID(
x,
{1,
6},
4
),
3,
,
":"
)*{-1,
1},
SUM
)*24
)
),
""
),
SUM
)
)
)Excel solution 8 for Calculate Daily Work Hours, proposed by Sunny Baggu:
=HSTACK(
TOCOL(
B1:H1
),
TOCOL(
BYCOL(
MAP(
B2:H6,
LAMBDA(a,
IFERROR(
MID(
a,
6,
2
) - MID(
a,
1,
2
) +
(MID(
a,
8,
2
) - MID(
a,
3,
2
)) / 60,
0
)
)
),
LAMBDA(
b,
SUM(
b
)
)
)
)
)Excel solution 9 for Calculate Daily Work Hours, proposed by Sunny Baggu:
=HSTACK(
TOCOL(B1:H1),
TOCOL(
BYCOL(
B2:H6,
LAMBDA(b,
SUM(
MAP(
b,
LAMBDA(a,
ABS(
SUM(
IFERROR(
SUM(
CEILING(--MID(a, 1, 4) / 100, 0.5),
FLOOR(--MID(a, 5, 5) / 100, 0.5)
),
0
)
)
)
)
)
)
)
)
)
)Excel solution 10 for Calculate Daily Work Hours, proposed by LEONARD OCHEA 🇷🇴:
=TOCOL(BYCOL(B2:H6,LAMBDA(x,SUM(IF(x>"",MID(x,{6,1,8,3},2)/{1,-1,60,-60},)))))Excel solution 11 for Calculate Daily Work Hours, proposed by LEONARD OCHEA 🇷🇴:
=TOCOL(BYCOL(B2:H6,
LAMBDA(c,
LET(M,
LAMBDA(
x,
MID(
c,
x,
2
)
),
SUM(IFERROR((M(
6
)-M(
1
)+(M(
8
)-M(
3
))/60),
))))))Excel solution 12 for Calculate Daily Work Hours, proposed by Md. Zohurul Islam:
=LET(
u,
B1:H1,
v,
B2:H6,
hdr,
HSTACK(
"Day",
"Total Hours"
),
w,
DROP(
REDUCE(
"",
u,
LAMBDA(
q,
p,
LET(
a,
FILTER(
v,
u=p
),
b,
FILTER(
a,
a>0
),
c,
MAP(
b,
LAMBDA(
x,
LET(
i,
CEILING(
TEXTSPLIT(
x,
"-"
)/100,
0.5
)*{-1,
1},
j,
BYROW(
i,
SUM
),
j
)
)
),
d,
SUM(
c
),
e,
VSTACK(
p,
d
),
f,
HSTACK(
q,
e
),
f
)
)
),
,
1
),
z,
VSTACK(
hdr,
TRANSPOSE(
w
)
),
z
)Excel solution 13 for Calculate Daily Work Hours, proposed by Pieter de B.:
=LET(
a,
B2:H6,
b,
B1:H1,
x,
LAMBDA(
y,
--LEFT(
y,
2
)&":"&RIGHT(
y,
2
)
),
z,
x(
TEXTAFTER(
B2:H6,
"-"
)
)-x(
TEXTBEFORE(
B2:H6,
"-"
)
),
GROUPBY(
TOCOL(
IFS(
z,
SEQUENCE(
,
COUNTA(
b
)
)
),
2
),
TOCOL(
IFS(
z,
z*24
),
2
),
SUM,
,
0
)
)Excel solution 14 for Calculate Daily Work Hours, proposed by Hamidi Hamid:
=LET(x,
IFERROR(
DROP(
TEXTSPLIT(
CONCAT(
"/"&A2:A6&"-"&B2:H6&"-"&B1:H1
),
"-",
"/"
),
1
),
0
),
f,
DROP(GROUPBY(TAKE(
x,
,
-1
),
(IFERROR(
CHOOSECOLS(
x,
3
)*1,
0
)-IFERROR(
CHOOSECOLS(
x,
2
)*1,
0
))/100,
SUM,
,
0),
1),
t,
TOCOL(
B1:H1
),
HSTACK(
t,
VLOOKUP(
t,
f,
2,
0
)
))Excel solution 15 for Calculate Daily Work Hours, pr&oposed by Asheesh Pahwa:
=REDUCE(A9:B9,B1:H1,LAMBDA(x,y,VSTACK(x,LET(f,FILTER(B2:H6,B1:H1=y),d,TEXT(RIGHT(f,4),"00:00")*24,l,TEXT(LEFT(f,4),"00:00")*24,HSTACK(y,SUM(TOROW(d-l,2)))))))Excel solution 16 for Calculate Daily Work Hours, proposed by ferhat CK:
=HSTACK(TOCOL(
B1:H1
),
TOCOL(BYCOL(IFERROR(MAP(B2:H6,
LAMBDA(x,
LET(b,
TEXTSPLIT(
x,
"-"
),
c,
LEFT(
b,
2
)&":"&RIGHT(
b,
2
),
(TAKE(
c,
,
-1
)-TAKE(
c,
,
1
))*24))),
0),
SUM)))Excel solution 17 for Calculate Daily Work Hours, proposed by Meganathan Elumalai:
=LET(a,TOCOL,b,REPLACE,HSTACK(a(B1:H1),a(BYCOL(B2:H6,LAMBDA(x,SUM(IFERROR(b(RIGHT(x,4),3,,":")-b(LEFT(x,4),3,,":"),))*24)))))Excel solution 18 for Calculate Daily Work Hours, proposed by Eddy Wijaya:
=LET(
t,
TOCOL(
B1:H1
),
c,
MAP(B2:H6,
LAMBDA(m,
LET(
s,
TEXTSPLIT(
m,
"-"
),
cv,
IF(
RIGHT(
s,
2
)="30",
SUBSTITUTE(
s,
"30",
"50"
),
s
),
IFERROR((TAKE(
cv,
,
-1
)-TAKE(
cv,
,
1
))/100,
0)))),
HSTACK(
t,
TOCOL(
BYCOL(
c,
SUM
)
)
))Excel solution 19 for Calculate Daily Work Hours, proposed by Peter Bartholomew:
= LET(
hours, 24 * BYCOL(MAP(shifts, DURATIONλ), SUM),
TRANSPOSE(VSTACK(weekday, hours))
)
where the function DURATIONλ refers to
= LAMBDA(interval,
LET(
// Extract digits in pairs and stack hour and minute columns
values, WRAPROWS(REGEXEXTRACT(interval, "d{2}", 1), 2),
// Combine hours and minutes to give Excel time value.
// Difference for duration.
duration, SUM({-1;1} * TIME(TAKE(values, , 1), DROP(values, , 1), {0;0})),
IFERROR(duration, 0)
)
)Excel solution 20 for Calculate Daily Work Hours, proposed by Ahmed Ariem:
=LET(DATA,TRANSPOSE(B1:H6), HSTACK(CHOOSECOLS(DATA,1),BYROW((TEXTAFTER(DROP(DATA,,1),"-",,,,0)-TEXTBEFORE(DROP(DATA,,1),"-",,,,0))/100,SUM)))Excel solution 21 for Calculate Daily Work Hours, proposed by Hussain Ali Nasser:
=TRANSPOSE(
VSTACK(
B1:H1,
MAP(
BYCOL(
B2:H6,
ARRAYTOTEXT
),
LAMBDA(
x,
LET(
_day,
TEXTSPLIT(
x,
"-",
", ",
1
),
_time,
TIMEVALUE(
LEFT(
_day,
2
) & ":" & RIGHT(
_day,
2
)
),
_diff,
SUM(
TAKE(
_time,
,
-1
) - TAKE(
_time,
,
1
)
) * 24,
_diff
)
)
)
)
)Excel solution 22 for Calculate Daily Work Hours, proposed by Jorge Alvarez:
=LET(d;TRANSPONER(B1:H1);
tt;TRANSPONER(BYCOL(B2:H6;LAMBDA(v;SUMA(SI.ERROR(
(ENTERO(DERECHA(v;4)/100)+RESIDUO(DERECHA(v;4);100)/60)-
(ENTERO(IZQUIERDA(v;4)/100)+RESIDUO(IZQUIERDA(v;4);100)/60);0)
)
)
));
APILARH(d;tt))Excel solution 23 for Calculate Daily Work Hours, proposed by Tsiory RAZAFITSEHENO:
=TRANSPOSE(BYCOL(B2:H6,
LAMBDA(a,
LET(end,
(TEXTAFTER(
a,
"-",
,
,
,
0
)),
start,
(TEXTBEFORE(
a,
"-",
,
,
,
0
)),
hour_end,
--(LEFT(
end,
2
)),
hour_start,
--(LEFT(
start,
2
)),
min_end,
--(RIGHT(
end,
2
))/60,
min_start,
--(RIGHT(
start,
2
))/60,
dif_hour,
hour_end-hour_start,
dif_min,
min_end-min_start,
SUM(
dif_hour
)+SUM(
dif_min
)))))Solving the challenge of Calculate Daily Work Hours with Python
Python solution 1 for Calculate Daily Work Hours, proposed by Konrad Gryczan, PhD:
import pandas as pd
from datetime import datetime
path = "639 Total Hours Per Day.xlsx"
input = pd.read_excel(path, usecols="A:H", nrows=6)
test = pd.read_excel(path, usecols="A:B", skiprows=8, nrows=8)
input_long = input.melt(id_vars=['Name'], var_name='Day', value_name='Hours').dropna()
input_long[['from', 'to']] = input_long.pop('Hours').str.split('-', expand=True)
input_long[['from', 'to']] = input_long[['from', 'to']].apply(lambda x: pd.to_datetime(x.str[:2] + ':' + x.str[2:], format='%H:%M'))
input_long['hours'] = (input_long['to'] - input_long['from']).dt.total_seconds() / 3600
result = input_long.groupby('Day').agg({'hours': 'sum'}).reset_index()
result.columns = ['Day', 'Total Hours']
print(sorted(result)==(sorted(test))) # True
Solving the challenge of Calculate Daily Work Hours with Python in Excel
Python in Excel solution 1 for Calculate Daily Work Hours, proposed by Alejandro Campos:
from datetime import datetime
data = xl("A1:H6", headers=True)
calculate_hours = lambda r: (lambda s, e: (e - s).seconds / 3600)(*map(lambda t: datetime.strptime(t, '%H%M'), r.split('-'))) if r else 0
total_hours = {day: sum(calculate_hours(r) for r in data[day]) for day in data if day != 'Name'}
df_total_hours = pd.DataFrame(total_hours.items(), columns=['Day', 'Total Hours'])
Python in Excel solution 2 for Calculate Daily Work Hours, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A1:H6", True).drop(columns="Name")
def MyFun(col):
split = col.dropna().str.split("-", n=1, expand=True)
strt = pd.to_datetime(split[0], format="%H%M")
end = pd.to_datetime(split[1], format="%H%M")
return (end - strt).dt.total_seconds().sum() / 3600
result = df.apply(MyFun).reset_index()
result.columns = ["Day", "Total Hours"]
result
Python in Excel solution 3 for Calculate Daily Work Hours, proposed by Seokho MOON:
from datetime import datetime
days = df.columns[1:]
def working_hours(text):
if pd.isna(text):
return 0
time_format = "%H%M"
start_time, end_time = [
datetime.strptime(t.strip(), time_format) for t in text.split("-")
]
return (end_time - start_time).seconds / 3600
total_hours = df[days].applymap(working_hours).sum(axis=0)
res = pd.DataFrame({"Total Hours": total_hours}).reset_index(names="Day")
Solving the challenge of Calculate Daily Work Hours with R
R solution 1 for Calculate Daily Work Hours, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(hms)
path = "Excel/639 Total Hours Per Day.xlsx"
input = read_excel(path, range = "A1:H6")
test = read_excel(path, range = "A9:B16")
result = input %>%
pivot_longer(cols = -Name, names_to = "Day", values_to = "Hours") %>%
separate(Hours, into = c('from', 'to'), sep = '-') %>%
na.omit() %>%
mutate(across(c(from, to), ~ parse_date_time(paste0(substr(., 1, 2), ":", substr(., 3, 4)), orders = "HM"))) %>%
mutate(hours = as.numeric(difftime(to, from, units = "hours"))) %>%
summarise(`Total Hours` = sum(hours), .by = Day)
all.equal(result, test)
#> [1] TRUE
&&