Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculate time difference between 2 dates/time only working hours and exclude weekends and holidays

Hey,

 

I want to calculate the time difference between 2 dates formatted like this: 5-11-2020 12:30:14. It does only need to count the time difference between 8:00 and 17:00 and also exclude weekends(Saterday and Sunday) and Holidays(if possible). 
It also needs to be possible to get negative results like this; in hours: -34:13:43 or in seconds: -14543.

For example between: 

Friday 25-2-2022 at 16:00 and Monday 28-2-2022 at 11:00. The answer needs to be 4 hours(I prefer the answer to be in seconds, so 14400).

 

Can someone help me?

 

 

 

Need Help

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      When I try this I get the following error: The SWITCH function does not support comparing values of type Number with values of type True/False. Consider using the VALUE or FORMAT function to convert one of the values.

       

      Can anyone help me with this problem?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    I do not understand why the 25th 16:00 to 28th 11:00 is 4 hours. And I got 19 hours after calculation. 

    I have created a simple sample, please refer to it to see if it helps you.

    Create a measure.

     

    Measure =
VAR _start =
    MAX ( Sheet2[date] )
VAR _if_start =
    IF ( MAX ( Sheet2[_count] ) = BLANK (), BLANK (), _start )
VAR _end =
    CALCULATE (
        MAX ( Sheet2[date] ),
        FILTER (
            ALL ( Sheet2 ),
            Sheet2[column-]
                = SELECTEDVALUE ( Sheet2[column-] ) - 1
                && Sheet2[_count] <> BLANK ()
        )
    )
VAR _if_end =
    IF ( MAX ( Sheet2[_count] ) = BLANK (), BLANK (), _end )
RETURN
    _if_start - _if_end

     

     

    I have also found an interesting video, please refer to it to see if it helps you.

    PowerBI: Calculate business/working days between two dates in a new column 

     

    If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      "I do not understand why the 25th 16:00 to 28th 11:00 is 4 hours. "

      It is because the workday ends on 17:00 on friday. So that is 1 hour.
      Then the workday starts again at 8:00 on monday untill 11:00. So that is 3 hours.
      Like this the total is 4 hours.

       

      The solution like you providede doesn't work for my case...

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Please refer to my pbix file to see if it helps you.

    Create columns.

    _count =
VAR work_time =
    IF (
        Sheet2[werkuren] = "start",
        DATEVALUE ( Sheet2[date] ) + TIME ( 8, 0, 0 ),
        DATEVALUE ( Sheet2[date] ) + TIME ( 17, 0, 0 )
    )
RETURN
    IF ( Sheet2[weekday_] <= 5, work_time, BLANK () )

    work_time =
IF (
    Sheet2[werkuren] = "start",
    DATEDIFF ( Sheet2[_count], Sheet2[date], SECOND ),
    DATEDIFF ( Sheet2[date], Sheet2[_count], SECOND )
)

     

    If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.