Date Diff in Hours : Minutes excluding weekends..

[Anonymous]

Hi I was struggling to calculate the time difference in hours:minutes and have got a perfect solution and it is working perfectly.

Is it possible to exclude the weekends while using the below query?

Diff2 =
VAR DiffInMinutes =
    DATEDIFF ( Table1[Start]; Table1[End]; MINUTE )
VAR DiffInHours =
    QUOTIENT ( DiffInMinutes; 60 )
VAR ModuloDiffInMinutes =
    MOD ( DiffInMinutes; 60 )
VAR Result =
    FORMAT ( DiffInHours; "00" ) & ":"
        & FORMAT ( ModuloDiffInMinutes; "00" )
RETURN
    Result

@Nolock  Kindly help...

[Nolock]

Hi @Anonymous,

I expect that neither start nor end timestamp is on the weekend. In the other case my code wouldn't work. If it can happen that start or end are at the weekend please explain what behavior you expect.

Create a new column which calculates all weekend days between A and B:

CountOfWeekdays = 
VAR tableOfDays = CALENDAR(Table1[Start]; Table1[End])
VAR tableOfWeekdays = FILTER(tableOfDays; WEEKDAY([Date]; 2) >= 6)
VAR countOfWeekdays = COUNTROWS(tableOfWeekdays)
RETURN
IF(ISBLANK(countOfWeekdays); 0; countOfWeekdays)

Remove weekends from the difference:

DiffWithoutWeekends = 
VAR DiffInMinutes = DATEDIFF(Table1[Start]; Table1[End]; MINUTE)
VAR DiffInHours = QUOTIENT(DiffInMinutes; 60)
VAR WeekendHours = 24 * Table1[CountOfWeekdays]
VAR DiffInHoursWithoutWeekend = DiffInHours - WeekendHours
VAR ModuloDiffInMinutes = MOD(DiffInMinutes; 60)
VAR Result = FORMAT(DiffInHoursWithoutWeekend; "00") & ":" & FORMAT(ModuloDiffInMinutes; "00")
RETURN
Result

And the result:

PowerBI file with the solution: PowerBI file

[Anonymous]

Thank you for replying.. This script is for calculating how much duration is taken to close an IT Ticket excluding weeknd hours.

If the Start falls in weekend then we should exclude the Start Time hours should be excluded while calculating the time difference. Similarly if the End Falls in weekend then End hours should be excluded while calculating the time difference.

Is this possible?

[Nolock · Accepted solution]

Hi @Anonymous,

I've implemented your additional requirements for handling hours at weekends. You need some new columns:

First of all, you need to find a new Start timestamp. If it is Saturday or Sunday, just use the start of tomorrow.

NextPossibleStart = 
IF (
    WEEKDAY ( Table1[Start]; 2 ) >= 6;
    DATEADD ( Table1[Start].[Date]; 1; DAY );
    Table1[Start]
)

Then you need to find the start of a day if the end timestamps is Saturday or Sunday.

PreviousPossibleEnd = 
VAR IsWeekend =
    WEEKDAY ( Table1[End]; 2 ) >= 6
VAR NewEndDate =
    IF ( IsWeekend; Table1[End].[Date]; Table1[End] )
VAR IsNewEndDateBeforeStartDate = NewEndDate < Table1[NextPossibleStart]
RETURN
    IF ( IsNewEndDateBeforeStartDate; Table1[NextPossibleStart]; NewEndDate )

Use these 2 new columns in finding all weekend days between 2 dates.

CountOfWeekdays = 
VAR tableOfDays =
    CALENDAR ( Table1[NextPossibleStart]; Table1[PreviousPossibleEnd] )
VAR tableOfWeekdays =
    FILTER ( tableOfDays; WEEKDAY ( [Date]; 2 ) >= 6 )
VAR countOfWeekdays =
    COUNTROWS ( tableOfWeekdays )
RETURN
    IF ( ISBLANK ( countOfWeekdays ); 0; countOfWeekdays )

And also in the diff:

DiffWithoutWeekends = 
VAR DiffInMinutes =
    DATEDIFF ( Table1[NextPossibleStart]; Table1[PreviousPossibleEnd]; MINUTE )
VAR DiffInHours =
    QUOTIENT ( DiffInMinutes; 60 )
VAR WeekendHours = 24 * Table1[CountOfWeekdays]
VAR DiffInHoursWithoutWeekend = DiffInHours - WeekendHours
VAR ModuloDiffInMinutes =
    MOD ( DiffInMinutes; 60 )
VAR Result =
    FORMAT ( DiffInHoursWithoutWeekend; "00" ) & ":"
        & FORMAT ( ModuloDiffInMinutes; "00" )
RETURN
    Result

Some tests:

And you can also download the PowerBI file again, I've uploaded the new version of it.

[BrunoS]

Hi Nolock

I'm trying to download the PowerBI File with your solution to this topic but the download link does not work fo me. Can you kindly post the file again?

Kind regards

Bruno

[Anonymous]

You are amazingly awesome.. Thank you so much and working perfectly..

[Jcarbo]

Hi all.

 I have the need to exclude from the time difference in 
Hours and Minutes Calculated here the non Office working hours.  is there someone who can give me some help on how to do that?

WHstart  and WHend could be a variable used to calculate that