KNOWLEDGE BASE

Calculating Business Hours Between Two Timestamps


Published: 09 Sep 2016
Last Modified Date: 18 May 2017

Question

How to calculate the number of business hours between two timestamps, for example to calculate the time spent on something based on a timestamp for submission ([New]) and when it was completed ([Processed]). 
 

Environment

Tableau Desktop

Answer

This solution assumes you have time stamps for the start and end of a particular action, referred to here as [Action Start] and [Action End]. 

Step 1:

Create parameters to mark the start and end of business hours. This will allow you to adjust business hours as needed. 
  1. Right-click in the data pane and select Create Parameter
  2. Name the parameter Day Open Hour, enter the following values, then click OK: 
    • Data type: Integer
    • Allowable Values: Range
    • Minimum: 1 
    • Maximum: 12 
    • Step Size: 1 
  3. Right-click in the data pane and select Create Parameter.
  4. Name the parameter Day Open Minute, enter the following values, then click OK: 
    • Data type: String 
    • Allowable Values: List 
    • List of Values: "00", "15", "30", and "45" 
  5. Repeat the above two steps for Day Close Hour and Day Close Minute. 
Note: The above steps are based on a 12 hour clock and starting and ending times in 15 minute intervals. Adjust as necessary (0-24 instead of 1-12 for hours,every 10 instead of every 15 minutes, etc.) for your schedule. 

Step 2:

Create calculations to round to the next business day. 
 
  1. Select Analysis > Create Calculated Field 
  2. Name the field "Action Start: Rounded", enter the following calculation, then click OK: 
     
    IF (DATEPART('hour', [Action Start]) * 60) + DATEPART('minute', [Action Start])  > (60 * (12 + [Day Close Hour]) ) + INT([Day Close Minute])
    THEN
    // created after End of Day so round to start of next business day
    DATEADD('minute', (60 * (24 + [Day Open Hour])) + INT([Day Open Minute]), DATETRUNC('day',[Action Start])) 
    ELSEIF
    (DATEPART('hour', [Action Start]) * 60) + DATEPART('minute', [Action Start])  < (60 * [Day Open Hour]) + INT([Day Open Minute])
    THEN
    // created before Start of Day so round to start of business day
    DATEADD('minute', (60 * [Day Open Hour]) + INT([Day Open Minute]), DATETRUNC('day',[Action Start])) 
    ELSE
    [Action Start]
    END
  3. Select Analysis > Create Calculated field, name the field "Action Start: Round to Weekday" and enter the following calculation to check if the new rounded timestamps fall on a weekend:
    IF DATENAME('weekday',[Action Start : Rounded]) = "Saturday" 
    THEN 
    // move to start of day monday by adding two days (48 hrs) and the monday morning hours
    DATEADD('minute', (60 * (48 + [Day Open Hour])) + INT([Day Open Minute]), DATETRUNC('day', [Action Start : Rounded]))
    ELSEIF DATENAME('weekday',[Action Start : Rounded]) = "Sunday" 
    THEN
    // move to start of day monday by adding a day (24 hrs) and the monday morning hours
    DATEADD('minute', (60 * (24 + [Day Open Hour])) + INT([Day Open Minute]), DATETRUNC('day', [Action Start : Rounded]))
    ELSE 
    [Action Start : Rounded]
    END
  4. Repeat the above two calculations for [Action End].

Step 3: 

Create calculations to find the difference in time. 
 
  1. Create the below calculation and name it "Start: Time to end of day"
    DATEDIFF(  'minute',
        [Action Start: Round to Weekdays], 
        DATEADD('minute', (60 * (12 + [Day Close Hour])) + INT([Day Close Minute]), DATETRUNC('day',[Action Start: Round to Weekdays]))
    )
  2. Repeat the above calculation with Day Open Hour and Minute and name it "End: Time from Start Of Day"
  3. Create three DATEDIFF calculations to help when calculating the final result, one for minutes, days, and weeks:
    "DateDiff Days"
    DATEDIFF('day', [Action Start: Round to Weekdays], [Action End : Round to Weekdays])
    "DateDiff minutes"
    DATEDIFF('minute', [Action Start: Round to Weekdays], [Action End : Round to Weekdays])
    "DateDiff weeks"
    DATEDIFF('week', [Action Start: Round to Weekdays], [Action End : Round to Weekdays])
  4. Create one final calculation to put all the pieces together:
     
    IF DATETRUNC('day', [Action Start: Round to Weekdays]) = DATETRUNC('day', [Action End : Round to Weekdays])
    THEN
    // the same day so just return the minutes between the two times on the same day
    [DateDiff minutes]
    ELSEIF DATETRUNC('week', [Action Start: Round to Weekdays]) = DATETRUNC('week', [Action End : Round to Weekdays])
    THEN
    // the two days are in the same week, so add the time on the created day, the time on the end day, plus a full day for each day between
    (([DateDiff days] - 1) * [Minutes per workday] ) + [Start: Time to end of day] + [End : Time from start of day ]
    ELSE
    // the days are in two different weeks, so get the same calc as above, and subtract the weekends between
    // weekends between would be datediff weeks * 2 * work minutes per day
    (([DateDiff days] - 1 - ([DateDiff weeks] * 2)) * [Minutes per workday] ) + [Start: Time to end of day] + [End: Time from start of day ]
    END

Additional Information

See the attached packaged workbook for an example. 

In summary, the above steps round all timestamps so that if they fall outside the normal daily business hours, they will be rounded up tot he start of the next business day. 
Did this article resolve the issue?