KNOWLEDGE BASE

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


Published: 18 Apr 2017
Last Modified Date: 19 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

Answer

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 AND DATEPART('hour', [Date Time Field]) < 10)
    OR (DATEPART('hour', [Date Time Field]) = 10 AND DATEPART('minute', [Date Time Field]) = 0)
    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"
    END
    
  3. Click OK.

Step 2: Build the view

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

Additional Information

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.
Did this article resolve the issue?