Calculate the work hours between 2 dates( datetime) excluding weekends - 9 am - 6pm Mon-Fri

Hi Team,

I am working with an issue where I need to calculate the difference between the 2 dates (datetime format) excluding weekends and non work hours. The work hours to be considered are between 9 am and 6pm , Mon - Fri .

The user wants to have a filter on the Start date.

I have tried a couple of solutions posted on the Power BI community , but they dont seem to be giving me the expected results.

For example in the dataset for Id = 17 , work hours should be close to 2 hours , considering the appliation was created on a Sunday.

Attaching my pbix file and the data extract.
TestData.xlsx (11.3 KB) Network_hours.pbix (119.7 KB)

Appreciate all the help.

Hi @Sim2312,

Please go through below solution. Hopefully, it will work in your case.

Hi Hafiz,

I am working with datetime datatypes and need the difference in hours and not days. Also my requirement is more specific - Work hours = Mon - Fri / 9 am - 6 pm. This makes it more complex to calculate.( Maybe easy for a Dax pro , I am still learning DAX and getting my head around it)

I have attached my data extract and the pbix file I am working with.

Can you please help.

Hi @Sim2312,

Please check out this forum topic, it refers to a Power Query function to calculate the time between two dates excluding Non working hours, weekends, holidays in either hours or minutes.

Hi @Sim2312,

I think solution provided by @Melissa exactly matches with your requirements. Hopefully, it will solve your issue.

In case of any confusion in implementing this, you can always ask again :slight_smile:

Hi Melissa & Hafiz,

Thank you so much for directing me in the right path. But I somehow couldnt get the function to work. I have followed the exact steps.

Also my requirement has been simplified as below so the function wouldnt work anymore in my case.

For weekdays consider - all hours ie if startdate is 1/01/2020 4pm and enddate is 2/01/2020 5pm then total hours = 25.
If Weekends exclude hours between Friday midnight untill Monday 9 am. ie if startdate is 04/01/2020 4pm and enddate is 06/01/2020 11am then total hours = 2.

Please help me out.

Hi @Sim2312

The testdata file along with working pbix file is attached for your reference.

Kumail Raza
Network_hours.pbix (48.4 KB) TestData.xlsx (26.3 KB)



Great - nice job! :+1:

One reconmendation. To get the errors out, you can add a try ~ otherwise clause like so:

eDNA - Network hours.pbix (48.9 KB)

1 Like

Hi Kumail & Melissa ,

Thanks for the above solution which works great. But as I said my requirements are as below:
Consider weekdays as full days.
Exclude the hours between Friday 6pm and Monday 9 am in the datediff cals.

Appreciate all the help

Hello @Sim2312

Atleast it gives solution to your first set of requirements that can be accepted as solution.

The new requirement would need some custom modification from scratch.

Hi Kumail,

Yes , your solution satisfies my first set of requirements perfectly ! Thanks so much , appreciate all the effort.

As for the second set of requirements, I am working on a solution in SQL , which I am more comfortable with.

Thanks again !!

1 Like


You’re welcome.

Best wishes,
Kumail Raza

Hi @Sim2312, just a friendly reminder, if your original question has been answered within the forum it is important to mark your thread as ‘solved’. If you have a follow question or concern related to this topic please start a new topic. More details can be found here - Asking Questions On The Enterprise DNA Support Forum.