Calculate business hours between two dates, excluding weekends in Tableau

Question

In Tableau, how to calculate business hours/mins between two dates, excluding weekends?

For example, my business hour is 9am-6pm Monday to Friday. You want to calculate what's the processing time between 05/10/2018 10am and 05/14/2018 11am for your team only count the business hours. The answer is 19 hours, but how to do that in Tableau?

Answer 1

This is a very common question always has been asked, but really there is no full answer around. Below is my solution.

1.Call it [Weekends Count]. Find out how many weekends between this two time

int( (DATEDIFF('day',[Start],[End])+DATEPART('weekday',[Start])) /7 )
*2
+ (if DATEPART('weekday',[Start]) = 1 then 1 else 0 end)
- (if DATEPART('weekday',[End]) = 7 then 1 else 0 end)

2.Call it [Start hour], calculate the hour difference of your [Start] hour to your business closing time (replace 18(6pm) to your closing time, and replace 9 (your business hour duration) with your number). (my hour here, 9am-6pm)

IF
18 - DATEPART('hour',[Start]) >0 and 18 - DATEPART('hour',[Start]) <=9
and DATEPART('minute',[Start]) > 0
then 18 - DATEPART('hour',[Start]) - 1

elseif 18 - DATEPART('hour',[Start]) >0
and DATEPART('minute',[Start]) = 0
then 18 - DATEPART('hour',[Start])

elseif 18 - DATEPART('hour',[Start]) <= 0 then 0

elseif 18 - DATEPART('hour',[Start]) >9
then 9
END

3.Call it [Start Min], same logic as #2 for mins, replace 18 with your closing hour

if [Start Hour] > 0
and DATEPART('minute',[Start]) != 0
then 60 - DATEPART('minute',[Start])

elseif [Start Hour] = 0 and
DATEPART('hour',[Start]) < 18
then 60 - DATEPART('minute',[Start])

elseif [Start Hour] = 0 and
DATEPART('hour',[Start]) >= 18
then 0

elseif DATEPART('minute',[Start]) = 0
then 0 
END

4.Call it [End Hour]. same as #2 to find out difference between your start hour to your [End] hour

IF
DATEPART('hour',[End]) >= 9 then DATEPART('hour',[End]) - 9
END

5.Call it [End Min], same as #4 to find out difference between your start min to your [End] min

DATEPART('minute',[End])

6.Call it [Total Hour], calculate the total hour. Two parts, first part is if [Start] and [End] are on the same date, then just use the difference. Second part, if they are not on the same date, then a formula like this (replace 9 with your business hour duration again) sum(hour of Start and End) + days in between * business hours/day – weekends in between * business hour/day

if
DATEDIFF('day',[Start],[End])=0 and
datepart('hour',[Start])- datepart('hour',[End]) =0
then
0

elseif
DATEDIFF('day',[Start],[End])=0 and
datepart('hour',[Start])- datepart('hour',[End]) !=0 and
datepart('minute',[Start])- datepart('minute',[End])<=0
then
DATEPART('hour',[End]) - DATEPART('hour',[Start])

elseif  DATEDIFF('day',[Start],[End])=0 and
datepart('hour',[Start])- datepart('hour',[End]) !=0 and
datepart('minute',[Start])- datepart('minute',[End])>0
then
DATEPART('hour',[End]) - DATEPART('hour',[Start]) -1

elseif
DATEDIFF('day',[Start],[End]) > 0
then
([Start Hour]+[End Hour])+
(DATEDIFF('day',[Start],[End]) -1)
* 9
- [Weekends Count]*9
END

7.Call it [Total Min], calculate the total minutes

if DATEDIFF('day',[Start],[End]) =0
and DATEPART('minute',[End])-DATEPART('minute',[Start])>=0
then DATEPART('minute',[End])-DATEPART('minute',[Start])

elseif DATEDIFF('day',[Start],[End]) =0
and DATEPART('minute',[End])-DATEPART('minute',[Start])<0
then 60 + DATEPART('minute',[End])-DATEPART('minute',[Start])

elseif DATEDIFF('day',[Start],[End]) !=0
then [Start Min]+[End Min]
end

DONE, that’s the difference of your total hour and total min between two timestamp, and work only during your business hours without weekends. Let me know if there is any typo or bugs.