KNOWLEDGE BASE

## Calculating Business Hours Between Two Timestamps

Published: 09 Sep 2016

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

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].  The below steps are demonstrated in the attached workbook.

### 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:
• Maximum: 12
• Step Size:
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 a calculation to track the number of minutes per workday and name it "minutes per workday"
```(60 * (12 + [Day Close Hour (pm)] - [Day Open Hour (am)]) + INT([Day Close Minute (pm)]) -
INT([Day Open Minute (am)]))```
5. 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```