Power BI - Calculate working hours between two dates

Let’s consider a scenario, we are creating a report for a lending company, and they want to know how many working hours it took for an application to get closed. The company operates from Monday to Friday 9am to 6pm (9 hours a day).

Let’s check our data,

We have Application ID, Loan Product, Apply date and Closed date. We want to calculate working hours between the Apply date and Closed date. Let’s get started with it.

Calendar = CALENDAR(MIN('Application'[Apply Date]),MAX('Application'[Closed Date]))

First, we need to create a calendar table, which starts from minimum apply date to maximum closed date so we have all dates that fall in our data. Now we need to add a calculated column which specifies if it’s a working day or not.

IsWorking = IF(WEEKDAY('Calendar'[Date],2)<=5,TRUE(),FALSE())

We have used WEEKDAY() function to identify which day of the week it is.

Syntax - WEEKDAY(<date>, <return_type>)

Return type 1: Starts from Sunday (1) to Saturday (7)

Return type 2: Starts from Monday (1) to Sunday (7)

For this function the default return type is 1, but in our scenario, we want it to be from Monday to Sunday, so we took the return type as 2.

This query will return true if it’s a weekday and false it it’s a weekend.

Now let’s go back to our table and calculate the working hours.

We have used the DATEDIFF() function to calculate total hours between these 2 dates. But we only want to calculate hours for the working days.

Now the 2nd variable calculates total hours between those two dates excluding the weekends.

But it still doesn’t give us the desired result as we work only 9 hours a day.

Let’s do some math,

working hours = 9 hence our non-working = 24-9 = 15

So, currently we are calculating 15 hours extra per day. Let’s see the final calculation.

Working Hours = 
var totalhrs = DATEDIFF('Application'[Apply Date],'Application'[Closed Date],HOUR)

var weekday_totalhrs = CALCULATE(totalhrs, 'Calendar'[IsWorking]= TRUE)

var non_working_hrs_on_working_days = (CALCULATE(COUNTROWS('Calendar'),DATESBETWEEN('Calendar'[Date],'Application'[Apply Date],'Application'[Closed Date]),'Calendar'[IsWorking]= TRUE)-1)*15

return
weekday_totalhrs - non_working_hrs_on_working_days

Here’s our final calculation. The 3rd variable is calculating those extra hours, that we need to subtract from weekday_totalhrs.

Let’s see this calculation closely.

First, we are calculating number of days between these two dates (only working days) then we are subtracting 1 from it, now you may ask why?

It’s because we don’t want to subtract 15 hours from both the first and the last day, we just want to exclude 6 hours from day 1 (6pm-12am) and 9hours form the last day (12am to 9am), so we consider those 2 days as 1. Finally, we multiply it by 15 and the final result is “weekday_totalhrs” minus “non_working_hrs_on_working_days”.

Here’s the result.

And the result is just what we wanted.

Note - This logic is not specific to 9-6pm working hours, it can be 10-7pm, 2-11pm or anything but if the number of working hours is not 9 then we need to do the math and subtract respective hours.

Here’s a tip for you.

If you want this calculation to be more accurate, then calculate the expressions in minutes and the divide the final result by 60. This is what it looks like.

Working Hours (in decimal) = 
var totalhrs = DATEDIFF('Application'[Apply Date],'Application'[Closed Date],MINUTE)

var actualtotalhrs = CALCULATE(totalhrs, 'Calendar'[IsWorking]= TRUE)

var non_working_hrs_on_working_days = (CALCULATE(COUNTROWS('Calendar'),DATESBETWEEN('Calendar'[Date],'Application'[Apply Date],'Application'[Closed Date]),'Calendar'[IsWorking]= TRUE)-1)*15*60

return
(actualtotalhrs - non_working_hrs_on_working_days)/60

That’s all for this blog, if you have any queries send an email to bi@ishandeshpande.com and I’ll get back to you. See you in the next blog!