cancel
Showing results for
Did you mean: Frequent Visitor

## Calculate number of hours between 2 dates excluding Weekends

I have a specific scenario where I need to calculate the number of hours between 2 dates excluding Weekends.

we get orders that move through different stages and we want to calculate how much time each stage is taking on an average.

For example , for an order the step 1 completed on  30th Dec' 2022 at 11 PM

while the step 2nd completed on  2nd Jan 2022 at 8 AM.

The time taken for step 2 should be shown as 9 hours ( Excluding Saturday and Sunday ) and not 57 hours.

This should be accounted for while calculating all averages.

I really appreciate the help here 1 ACCEPTED SOLUTION  Community Support

Hi @Vivek26 ,

Assuming that the dates of all steps are workdays, try this:

``````Hours =
VAR _time_1 =
CALCULATE (
MAX ( 'Table'[Status Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Order NO] = EARLIER ( 'Table'[Order NO] )
&& 'Table'[Step] < EARLIER ( 'Table'[Step] )
)
)
VAR _hours =
IF (
ISBLANK ( _time_1 ),
0,
DIVIDE ( DATEDIFF ( _time_1, 'Table'[Status Date], SECOND ), 3600 )
- MAX (
DATEDIFF ( _time_1, 'Table'[Status Date], DAY ) + 1
- NETWORKDAYS ( _time_1, 'Table'[Status Date] ),
0
) * 24
)
RETURN
_hours`````` Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

2 REPLIES 2  Community Support

Hi @Vivek26 ,

Assuming that the dates of all steps are workdays, try this:

``````Hours =
VAR _time_1 =
CALCULATE (
MAX ( 'Table'[Status Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Order NO] = EARLIER ( 'Table'[Order NO] )
&& 'Table'[Step] < EARLIER ( 'Table'[Step] )
)
)
VAR _hours =
IF (
ISBLANK ( _time_1 ),
0,
DIVIDE ( DATEDIFF ( _time_1, 'Table'[Status Date], SECOND ), 3600 )
- MAX (
DATEDIFF ( _time_1, 'Table'[Status Date], DAY ) + 1
- NETWORKDAYS ( _time_1, 'Table'[Status Date] ),
0
) * 24
)
RETURN
_hours`````` Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum  Helper I

hi @Vivek26  have you tried to revert the Step values (rows) to a column? this will provide you with the opportuinity to create a measure or a calculated field with a Date Difference between steps, according to your needs.  