Forum Discussion
Date / Time difference excluding weekends and factoring working hours
Hello,
After seeing this following post and in order to not reopen it, I tried the function explained at the bottom but it returns sometimes suspicious values.
Remind, here's the function :
let
// calculates a duration between two timestamps in working hours
fnDurationWorkingHours = (StartDateTime as datetime, EndDateTime as datetime) as duration =>
let
// start and end date
StartDate = Date.From(StartDateTime),
EndDate = Date.From(EndDateTime),
// start and end time
StartTime = Time.From(StartDateTime),
EndTime = Time.From(EndDateTime),
// end of the shift on the first day and start of the shift on the last day
ShiftEndOnStartDate = #time(17, 0, 0),
ShiftStartOnEndDate = #time(8, 0, 0),
// working days are from Mon=0 to Fri=4
fnIsWorkingDay = (dt as date) as logical => Date.DayOfWeek(dt, Day.Monday) < 5,
// generate whole days between StartDateTime and EndDateTime
DaysBetween = List.Generate(
() => Date.AddDays(StartDate, 1),
each _ < EndDate,
each Date.AddDays(_, 1)
),
// select only working days
WorkingDaysBetween = List.Select(DaysBetween, fnIsWorkingDay),
// get minimum of two times
fnMin = (first as time, second as time) as time =>
if first > second then second else first,
// get maximum of two times
fnMax = (first as time, second as time) as time =>
if first > second then first else second,
// duration on the first day
StartDateWorkingHours =
if not fnIsWorkingDay(StartDate) then
#duration(0, 0, 0, 0)
else if ShiftEndOnStartDate < StartTime then
#duration(0, 0, 0, 0)
else
ShiftEndOnStartDate - fnMax(StartTime, ShiftStartOnEndDate),
// duration on the days between
WorkingHoursBetween = #duration(0, List.Count(WorkingDaysBetween) * 9, 0, 0),
// duration on the last day
EndDateWorkingHours =
if not fnIsWorkingDay(EndDate) then
#duration(0, 0, 0, 0)
else if EndTime < ShiftStartOnEndDate then
#duration(0, 0, 0, 0)
else
fnMin(EndTime, ShiftEndOnStartDate) - ShiftStartOnEndDate,
// sum it up
Result =
if StartDateTime > EndDateTime then
#duration(0, 0, 0, 0)
else if StartDate = EndDate and not fnIsWorkingDay(StartDate) then
#duration(0, 0, 0, 0)
else if StartDate = EndDate then
fnMin(EndTime, ShiftEndOnStartDate) - fnMax(StartTime, ShiftStartOnEndDate)
else
StartDateWorkingHours +
WorkingHoursBetween +
EndDateWorkingHours
in
ResultBut for example, when I have in start and end time these
There's no 6 days and 20h betweend 19/09/2022 and 13/10/2022, even if we exclude weekends.
Some other examples :
Could you help me please ?
Thank you!
so complicated...
(from, to, optional st, optional et) => [s_time = st ?? #time(8, 0, 0), e_time = et ?? #time(17, 0, 0), start = List.Min({from, to}), end = List.Max({from, to}), start_date = Date.From(start), end_date = Date.From(end), gen = List.Generate( () => [c = start_date, sod = List.Max({start, start_date & s_time}), eod = List.Min({end, c & e_time})], (x) => x[c] <= end_date, (x) => [c = Date.AddDays(x[c], 1), sod = c & s_time, eod = List.Min({end, c & e_time})], (x) => if Date.DayOfWeek(x[c], Day.Monday) > 4 then null else x[eod] - x[sod] ), result = if List.NonNullCount({from, to}) < 2 then null else List.Sum(gen)][result]
22 Replies
so complicated...
(from, to, optional st, optional et) => [s_time = st ?? #time(8, 0, 0), e_time = et ?? #time(17, 0, 0), start = List.Min({from, to}), end = List.Max({from, to}), start_date = Date.From(start), end_date = Date.From(end), gen = List.Generate( () => [c = start_date, sod = List.Max({start, start_date & s_time}), eod = List.Min({end, c & e_time})], (x) => x[c] <= end_date, (x) => [c = Date.AddDays(x[c], 1), sod = c & s_time, eod = List.Min({end, c & e_time})], (x) => if Date.DayOfWeek(x[c], Day.Monday) > 4 then null else x[eod] - x[sod] ), result = if List.NonNullCount({from, to}) < 2 then null else List.Sum(gen)][result]Great solution. Thanks for providing.
Hello,
I tried your solution but it seems to have problems in it :
Some results are wrong (between 3 and 17, there's not only 3 days 😞 )
that is DURATION where 1 day = 24 hours. If you want solution in (working) hours then apply Duration.TotalHours.
Hi,
I tried it but it seems I got an issue with start and end because you use #time... instead I could reuse existing field with datetime datatype.
I tried to replace them with #"début" and #"fin" or [début] and [fin] but I got an error with "field not recognized"
Provide sample data as I mentioned above please.
Thanks to both of you helping me to resolve my problems!!! 🙂
