Date / Time difference excluding weekends and factoring working ours

[Gplange]

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

[dufoq3]

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"

[artemus]

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])

[Nolock]

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)

[Gplange]

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 

[Nolock · Accepted solution]

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)

[Gplange]

@Nolock  Thank you. You have no idea how you've saved my life with this solution. THanks again 

[TimWilsens]

Hi, can you explain me how I can implement this in PowerBI? This would really help me out as I exactly like you need to calcualte the difference between two date/times exlcuding weekends.

Many thanks in advance!