Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Date Diff in Hours : Minutes excluding weekends..

  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 ...
Need Help
  • Nolock's avatar
    Nolock
    7 years ago

    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.

Nolock's avatar
Nolock
Icon for Resident Rockstar rankResident Rockstar
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    Nolock
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

      • Jcarbo's avatar
        Jcarbo
        New Member
        6 years ago

        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

    • BrunoS's avatar
      BrunoS
      New Member
      3 years ago

      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