Calculate Date and Time difference considering the weekends and workhours

dcs136

There may be several methods to get the expected result. I’ll divide the difference to three parts, the first day, the middle days and the last day. For details, please refer to following steps.

I’ve also upload my .pbix file here for reference.

1. Create a calendar table with following formula. But do not create relationship between these two tables.
   

   Calendar =
   CALENDAR ( "1/1/2016", "12/31/2016" )

2. Create a column in calendar table to mark the working days.
   

   WorkDay = 
   VAR WeekDayNum =
       WEEKDAY ( 'Calendar'[Date], 2 )
   RETURN
       (
           IF ( WeekDayNum = 6 || WeekDayNum = 7, FALSE (), TRUE () )
       )

3. Create a column to store the working end time of first day.
   

   FirstDayEndTime = 
   DATE ( YEAR ( Table1[DateTimeFrom] ), MONTH ( Table1[DateTimeFrom] ), DAY ( Table1[DateTimeFrom] ) )
   & " 18:00:00"

4. Create a column to calculate the working seconds of first day.
   

   FirstDaySecDiff = 
   DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND )

5. Create a column to store the working start time of last day.
   

   LastDayStartTime = 
   DATE ( YEAR ( Table1[DateTimeTo] ), MONTH ( Table1[DateTimeTo] ), DAY ( Table1[DateTimeTo] ) )
   & " 8:00:00"

6. Create a column to store the working end time of last day.
   

   LastDayEndTime = 
   DATE ( YEAR ( Table1[DateTimeTo] ), MONTH ( Table1[DateTimeTo] ), DAY ( Table1[DateTimeTo] ) )
   & " 18:00:00"

7. Create a column to calculate the working seconds of last day.
   

   LastDaySecDiff = 
   IF (
       FORMAT ( Table1[DateTimeFrom], "Short Date" )
           <> FORMAT ( Table1[DateTimeTo], "Short Date" ),
       IF (
           Table1[DateTimeTo] >= Table1[LastDayStartTime]
               && Table1[DateTimeTo] <= Table1[LastDayEndTime],
           DATEDIFF ( Table1[LastDayStartTime], Table1[DateTimeTo], SECOND ),
           IF (
               Table1[DateTimeTo] > Table1[LastDayEndTime],
               DATEDIFF ( Table1[LastDayStartTime], Table1[LastDayEndTime], SECOND ),
               0
           )
       ),
       0
   )

8. Create a column to calculate the working seconds of middle days.
   

   MidDaysSecDiff = 
   IF (
       FORMAT ( Table1[DateTimeFrom], "Short Date" )
           <> FORMAT ( Table1[DateTimeTo], "Short Date" ),
       3600 * 10
           * (
               CALCULATE (
                   DISTINCTCOUNT ( 'Calendar'[Date] ),
                   FILTER (
                       'Calendar',
                       'Calendar'[Date] > Table1[FirstDayEndTime]
                           && 'Calendar'[Date] < Table1[LastDayStartTime]
                           && 'Calendar'[WorkDay] = TRUE ()
                   )
               )
                   - 1
           ),
       0
   )

9. Create the final column to calculate the total working hours.
   

   TotalHourDiff = 
    ( Table1[FirstDaySecDiff] + Table1[LastDaySecDiff]
       + Table1[MidDaysSecDiff] )
   / 3600

Note: The data type of columns of “FirstDayEndTime”, “LastDayStartTime” and “LastDayEndTime” should be Date/Time as below.

Best Regards,

Herbert

Really appreciate the work y'all put in to get a working DAX solution. I turned it into a custom M function so that you can easily apply it to multiple columns without having a ton of duplication. Also fixed a couple of small bugs that were in the original DAX.

To use, just copy this into a new blank query and should work out of the box.

Of course you're free to change the start/end times (currently at 8am and 5pm) etc

(from, to) => let
    // utility functions
    dateWithSetTime = (dt, t) => DateTime.From(Date.ToText(Date.From(dt)) & " " & Time.ToText(t)),
    max2 = (first, second) => if first > second then first else second,
    min2 = (first, second) => if first < second then first else second,
    isBetween = (t, first, last) => t >= first and t <= last,
    isWeekend = (day) => Date.DayOfWeek(day) = 5 or Date.DayOfWeek(day) = 6,
    isWorkday = (day) => not isWeekend(day),
    numDaysBetween = (f, t) =>
        let
            start = Date.AddDays(Date.From(from), 1),
            length = Duration.Days(Date.From(to) - Date.From(from)) - 1,
            dates = List.Dates(start, length, #duration(1, 0, 0, 0)),
            workdays = List.Select(dates, each isWorkday(_))
        in
            List.Count(workdays),
    dur = (days) => Duration.From(days),
    
    // variables and initial setup 
    startTime = #time(8,0,0),
    endTime = #time(17,0,0),
    firstDayStartTime = dateWithSetTime(from, startTime),
    firstDayEndTime = dateWithSetTime(from, endTime),
    lastDayStartTime = dateWithSetTime(to, startTime),
    lastDayEndTime = dateWithSetTime(to, endTime),
    endsOnStartDay = Date.From(from) = Date.From(to),
    
    // get seconds for first, middle, and last days
    firstDaySeconds =
        // make sure that end date is after start date 
        if from > to then error "The start date is greater than the end date. Make sure that you passed the arguments in the right order." else
        // record 0 time if weekend, starts after day ends, or ends before day starts
        if isWeekend(Date.From(from)) or from > firstDayEndTime or to < firstDayStartTime
        then dur(0)
        else min2( to, firstDayEndTime ) - max2( from, firstDayStartTime ),
        
    middleDaysSeconds =
        if endsOnStartDay
        then dur(0)
        else (endTime - startTime) * numDaysBetween(from, to),
    
    lastDaysSeconds =
        // record 0 time if holiday, weekend, already counted, or ends before day starts 
        if isWeekend(Date.From(to)) or endsOnStartDay or to < lastDayStartTime 
        then dur(0)
        else min2(to, lastDayEndTime) - lastDayStartTime,
    
    totalSeconds = Duration.TotalSeconds(firstDaySeconds + middleDaysSeconds + lastDaysSeconds) 
in
    totalSeconds / 3600

Could someone help me find the error in this expression? I get blank values where there should be a count, but the 0s work for time differences on the same day.

MidDaysSecDiff =
IF (Table1[DateTimeFrom].[Date] <> Table1[DateTimeTo].[Date],
CALCULATE( DISTINCTCOUNT('Calendar2'[Date]), FILTER('Calendar2','Calendar2'[Date] > Table1[FirstDayEndTime] && 'Calendar2'[Date] < Table1[LastDayStartTime] && 'Calendar2'[WorkDay2] = TRUE())),0)

Hello Herbert,

 I am looking at  your solution, but can you have a look at my data? The FirstDaySecDiff, LastDaySecDiff  and MidDaysSecDiff are too large to be normal. I have no idea where is wrong. Thanks!!!

@dcs136 can you also look at my problem? have you occured this before?

Hi everyone, im recently working with power bi and dax, i have just been asked to do this calculation and im not very clear on how to implement the code, i think that understanding the model would help me, could someone help me please?.

regards,