KNOWLEDGE BASE

## Grouping Time by Hours and Minutes While Excluding Days, Months, or Years

Published: 18 Apr 2017

### Question

How to create groups of time by hours and minutes that ignores any date information.

For example 4/1/2017 9:05am and 4/2/2017 9:30am would both be in the "9:30 to 10:30" bin despite being on different days.

### Environment

Tableau Desktop

The attached example workbook uses the sample data set Superstore to demonstrate the following directions.

### Step 1: Create a calculated field

1. Select Analysis > Create Calculated Field...
2. Name the calculated field "Time Bins" and enter the below formula
```IF (
DATEPART('hour', [Date Time Field]) >= 7
//if the hour number is after or on 7
AND DATEPART('hour', [Date Time Field]) < 10
//and the hour number is before 10
)
OR (
DATEPART('hour', [Date Time Field]) = 10
//if the hour number is 10
AND DATEPART('minute', [Date Time Field]) = 0
//and the minute number is zero
)
THEN "7:00-10:00"

ELSEIF DATEPART('hour', [Date Time Field]) < 14
OR ( DATEPART('hour', [Date Time Field]) = 14
AND DATEPART('minute', [Date Time Field]) = 0 )
THEN "10:01-14:00"
ELSEIF DATEPART('hour', [Date Time Field]) < 18
OR ( DATEPART('hour', [Date Time Field]) = 18
AND DATEPART('minute', [Date Time Field]) = 0 )
THEN "14:01-18:00"
END
```
3. Click OK.

### Step 2: Build the view

1. Add the calculated field to the Rows shelf
2. (Optional) Add SUM(Sales) to Details

The DATEPART() function allows us to return a specific piece of a date time field. Because the DATEPART() function returns either the number of hours or the number of minutes as a standalone number, we have to write a condition that will specify both hour and minute.

To view the above steps in action, see the video below.
Note: the video has no sound.