Forum Discussion
Solved
Date / Time difference excluding weekends and factoring working ours
Im New to power BI and need help in translate excel formula below into a calculated column
The formula calclates working period between two dates excluding weekends and factors working hours
Example:
| A | B | C | |
| 1 | 17/05/2019 14:46 | 21/05/2019 11:39 | 14:53:14 |
formula
=(NETWORKDAYS(A1,B1)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A2,1),"17:00","8:00")
results: 14:53:14 (hh:mm:ss)
In the formula, A1 is the start date time, B1 is the end date time, 8:00 and 17:00 are the general start time and end time in each day
Hi Gplange,
this time I've tested more cases and I hope it works now.
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 Result, // test data StartDateTime = #datetime(2019, 5, 17, 18, 46, 0), EndDateTime = #datetime(2019, 5, 18, 18, 39, 0) in fnDurationWorkingHours(StartDateTime, EndDateTime)
7 Replies
Not 100% tested, but...
Add a custom column with the following code:
let workHoursThisWeek = (t) => let dayOfWeek = Date.DayOfWeek(t), timeOfDay = DateTime.Time(t) in if dayOfWeek = 0 then #duration(0,0,0,0) else if dayOfWeek = 6 then 45 * #duration(0,1,0,0) else ((dayOfWeek - 1) * 9 * #duration(0,1,0,0) + (( if timeOfDay < #time(8,0,0) then #time(8,0,0) else if timeOfDay > #time(17,0,0) then #time(17,0,0) else timeOfDay) - #time(8,0,0))) in (Date.StartOfWeek([B]) - Date.StartOfWeek([A])) * (9 * 5) / (7 * 24) - workHoursThisWeek([A]) + workHoursThisWeek([B])Hi Gplange,
I've written another code which is maybe more complicated, but straight forward. But I'd like to recommend you to use a separate date dimension because you can handle exceptions like holidays, bridge days, and so on.
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), // 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), // end of the shift on the first day and start of the shift on the last day ShiftEndOnStartDate = StartDate & #time(17, 0, 0), ShiftStartOnEndDate = EndDate & #time(8, 0, 0), // duration on the first day StartDateWorkingHours = if not fnIsWorkingDay(StartDate) then #duration(0, 0, 0, 0) else if ShiftEndOnStartDate < StartDateTime then #duration(0, 0, 0, 0) else ShiftEndOnStartDate - StartDateTime, // 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 EndDateTime < ShiftStartOnEndDate then #duration(0, 0, 0, 0) else EndDateTime - ShiftStartOnEndDate, // sum it up Result = StartDateWorkingHours + WorkingHoursBetween + EndDateWorkingHours in Result, // test data StartDateTime = #datetime(2019, 5, 17, 14, 46, 0), EndDateTime = #datetime(2019, 5, 21, 11, 39, 0) in fnDurationWorkingHours(StartDateTime, EndDateTime)Hello Nolock Thanks a lot .
It worked but i noticed the formula adds 9 hours to the time when the StartDate and Enddate are the same day . I did a quick check with results in Excel. It working perfectly when the days are different. Attached a screen shot I've highlighted the wrong ones in red and correct ones in green. Please help
Really appreciate the help . Thanks a gain
Hi TimWilsens,
another approach here (you can see how to use it at the bottom of this post).
You can edit Shift step according to your needs.
Result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc3bDYAgEETRVsx+kzCzPIRthdB/GyIaMfye3My0Jjw9klewHowWs0HFiXIpLVRjlu7a34sFGLDF56BbZxyX43W3q46ZWUM98uMos06jJj9lno9Jer8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Start", type datetime}, {"End", type datetime}}, "sk-SK"), // Edit according to your needs. Shift = [ start = #time(8, 0, 0), end = #time(17, 0, 0) ], StepBack = ChangedType, Ad_DaysWithoutWeekends = [ ls = List.Buffer(StepBack[Start]), le = List.Buffer(StepBack[End]), lg = List.Generate( ()=> [ x = 0, start = ls{x}, end = le{x} ], each [x] < List.Count(ls), each [ x = [x]+1, start = ls{x}, end = le{x} ], each List.Select( List.Transform( { Number.RoundDown(Number.From([start]), 0)..Number.RoundDown(Number.From([end]), 0) }, Date.From ), each Date.DayOfWeek(_, Day.Monday) < 5 //working days only ) ), merged = Table.FromColumns(Table.ToColumns(StepBack) & {lg}, Table.ColumnNames(StepBack) & {"Working Days"}) ][merged], Ad_WorkedDuration = Table.AddColumn(Ad_DaysWithoutWeekends, "Worked Duration", each [ fullDays = List.Count(List.RemoveLastN(List.RemoveFirstN([Working Days]))), fullDaysHours = if fullDays > 0 then fullDays * ( Time.Hour(Shift[end]) - Time.Hour(Shift[start]) ) else 0, firstDayTime = Shift[end] - Time.From([Start]), lastDayTime = Time.From([End]) - Shift[start], workedDuration = Duration.From(fullDaysHours / 24) + ( if fullDays = 0 then Time.From([End]) - Time.From([Start]) else firstDayTime + lastDayTime ) ][workedDuration], type duration), Ad_WorkedDays = Table.AddColumn(Ad_WorkedDuration, "Worked Days", each Duration.TotalDays([Worked Duration]), type number), Ad_WorkedHours = Table.AddColumn(Ad_WorkedDays, "Worked Hours", each Duration.TotalHours([Worked Duration]), type number), #"RemovedColumns" = Table.RemoveColumns(Ad_WorkedHours,{"Working Days"}) in #"RemovedColumns"
