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.

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors