Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have two columns - Created Date and Resolved Date. I'm trying to do a turn around (difference Resolved-Created) but I'd like to exclude any hours after 4:30PM M-F; and all hours of Saturday to Sunday - to get a true turn around time.
= Table.AddColumn(#"Added Custom", "Turn Around Time", each [ResolvedDate]-[Created])
Solved! Go to Solution.
let
// this is your table. Replace #table with a ref to your query name
tbl = #table(
type table [created = datetime, resolved = datetime],
{{#datetime(2024, 5, 7, 8, 0, 0), #datetime(2024, 5, 14, 16, 50, 0)},
{#datetime(2024, 5, 8, 7, 0, 0), #datetime(2024, 5, 12, 7, 30, 0)},
{#datetime(2024, 5, 4, 10, 0, 0), #datetime(2024, 5, 6, 16, 50, 0)}}
),
// this function calculates duration on-hours
// created = Created Date as datetime
// resolved = Resolved Date as datetime
// open = opening time as time (say, 8:00)
// close = closing time as time (say 16:30)
on_time = (created as datetime, resolved as datetime, open as time, close as time) =>
[
// days of week correction to get next working day
corr_open = {1, 1, 1, 1, 3, 2, 1},
gen = List.Generate(
() =>
[start =
if Time.From(created) >= close
or Date.DayOfWeek(created, Day.Monday) > 4
then Date.From(Date.AddDays(created, corr_open{Date.DayOfWeek(created, Day.Monday)})) & open
else if Time.From(created) < open
then Date.From(created) & open
else created,
end = List.Min({resolved, Date.From(start) & close})],
(x) => x[start] <= resolved,
(x) =>
[start = Date.From(Date.AddDays(x[end], corr_open{Date.DayOfWeek(x[end], Day.Monday)})) & open,
end = List.Min({resolved, Date.From(start) & close})],
(x) => x[end] - x[start]
),
out = Duration.TotalHours(List.Sum(gen))][out],
// here we add column with total hours
total_hours = Table.AddColumn(
tbl,
"Turn Around Time",
(x) => on_time(x[created], x[resolved], #time(8, 0, 0), #time(16, 30, 0)))
in
total_hours
let
// this is your table. Replace #table with a ref to your query name
tbl = #table(
type table [created = datetime, resolved = datetime],
{{#datetime(2024, 5, 7, 8, 0, 0), #datetime(2024, 5, 14, 16, 50, 0)},
{#datetime(2024, 5, 8, 7, 0, 0), #datetime(2024, 5, 12, 7, 30, 0)},
{#datetime(2024, 5, 4, 10, 0, 0), #datetime(2024, 5, 6, 16, 50, 0)}}
),
// this function calculates duration on-hours
// created = Created Date as datetime
// resolved = Resolved Date as datetime
// open = opening time as time (say, 8:00)
// close = closing time as time (say 16:30)
on_time = (created as datetime, resolved as datetime, open as time, close as time) =>
[
// days of week correction to get next working day
corr_open = {1, 1, 1, 1, 3, 2, 1},
gen = List.Generate(
() =>
[start =
if Time.From(created) >= close
or Date.DayOfWeek(created, Day.Monday) > 4
then Date.From(Date.AddDays(created, corr_open{Date.DayOfWeek(created, Day.Monday)})) & open
else if Time.From(created) < open
then Date.From(created) & open
else created,
end = List.Min({resolved, Date.From(start) & close})],
(x) => x[start] <= resolved,
(x) =>
[start = Date.From(Date.AddDays(x[end], corr_open{Date.DayOfWeek(x[end], Day.Monday)})) & open,
end = List.Min({resolved, Date.From(start) & close})],
(x) => x[end] - x[start]
),
out = Duration.TotalHours(List.Sum(gen))][out],
// here we add column with total hours
total_hours = Table.AddColumn(
tbl,
"Turn Around Time",
(x) => on_time(x[created], x[resolved], #time(8, 0, 0), #time(16, 30, 0)))
in
total_hours
I don't think there's a simple way to do this.
I think you'll be best splitting each date time column into two: date and time. Build a networkdays column to count the number of full work days between the two dates, (again there's no ready build function for this as far as I know, but if you search 'networkdays in power query' you will get instructions.) then another column to take the start time away from a full day - so say you work 9-5, and start time is 4, that'd be 17:00-16:00, giving 1 hour, then another column taking the start of the workday away from the finish time - so finishing at 4 on a 9-5 day would be 16:00-09:00, giving 7 hours, then add all 3 columns together.