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

[Sim2312]

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.

[hafizsultan]

Hi @Sim2312,

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

Dax Calculation for Number of Days excluding weekends and holidays

Hi @bimbbb, Please see if it serves your requirements. Total Workdays Not Holidays_V2.0 = SUMX ( FILTER ( ALL ( 'Dates' ), 'Dates'[Date] >= MAX ( 'Main_table'[Start Date] ) && 'Dates'[Date] <= MAX ( 'Main_table'[Last_day_calc] ) && 'Dates'[Date] <= MAX ( 'Dates'[Date] ) ), 'Dates'[Workday Not Holiday] ) Total Workdays_V2.0 = SUMX ( FILTER ( ALL ( 'Dates' ), 'Dates'[Date] >= MAX ( 'Main_table'[Start Date] ) &…

[Sim2312]

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.

[Melissa]

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.

Time format 37:30:55 greater than 24 hours

. Okay so Bill Szysz created a Power Query M function that does just that and here it is: (WHStart, WHEnd, Start, End, ListOfHolidays) => let DStart = Number.From(DateTime.Date(Start)), // start day TStart = Number.From(DateTime.Time(Start)), // start time DEnd = Number.From(DateTime.Date(End)), // end day TEnd = Number.From(DateTime.Time(End)), // end time // List of days without saturdays and sundays ListOfNumbe…

[hafizsultan]

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

[Sim2312]

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.

[Kumail]

Hi @Sim2312

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

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

[Melissa]

@Kumail

Great - nice job!

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

.
eDNA - Network hours.pbix (48.9 KB)

[Sim2312]

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

[Kumail]

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.

[Sim2312]

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 !!

[Kumail]

@Sim2312

You’re welcome.

Best wishes,
Kumail Raza

[EnterpriseDNA]

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.