KNOWLEDGE BASE

Isolate Time from Date and Time Field


Published: 11 Mar 2019
Last Modified Date: 25 Jul 2019

Question

How to create views which show time without date. For example, creating a schedule of classes, graphing the number of calls per hour over multiple days, or showing when employees clock in and out.

Environment

Tableau Desktop

Answer

CLICK TO EXPAND SOLUTION
Option 1: Use a Date & Time field and then format the values as time only
In the example workbook, employee work times are recorded as date and timestamps when employees clock in and out. The goal is to show the only the time the employees clock in and out each. These instructions begin from the "Opt 1: original" worksheet. Directions to create "Opt 1: original" are in the workbook.
  1. Right-click [Shift Start] on the Rows shelf and select Format…
  2. In the left-hand Format pane, in the dropdown menu for Dates, select custom and type in something like "h:mm AMPM" without quotes
  3. Repeat steps 1-2 for all Date & Time fields
For more custom date formats, see Custom Date Formats
CLICK TO EXPAND SOLUTION
Option 2: Use the Hour date part
  1. Right-click [Call Time] on the Rows shelf and select the first More > Hour

    Note: There are two sections in the right-click menu for dates that correspond to whether to show the date part, or the whole date truncated up to a particular level. In this example, we want the date part Hour, and not the whole date truncated to the nearest hour. To learn more see Changing Date Levels
     
  2. Right-click HOUR(Call Time) on the Rows shelf and select Format…
  3. In the left-hand Format pane, in the dropdown for Dates, select 12-Hour or the desired format
CLICK TO EXPAND SOLUTION
Option 3: Shift all date & time values to the same date
In the example workbook, calls are recorded at the timestamp they come in as a Date & Time field. The goal is to show the average number of calls per minute regardless of date. These instructions begin from the "Opt 3: original" worksheet. Directions to create "Opt 3: original" are in the workbook.
  1. Select Analysis > Create Calculated Field
  2. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "Call Time w/o Date"
    2. In the formula field, create a calculation similar to the following:
      DATEADD('day', DATEDIFF('day', [Call Time], TODAY()), [Call Time])
  3. Remove HOUR(Call Time) from the Rows shelf
  4. Right-click and drag [Call Time w/o Date] to the Rows shelf
  5. In the Drop Field dialog, select Call Time w/o Date (Continuous) and click OK
CLICK TO EXPAND SOLUTION
Option 4: Append all times to the same date to create a date field
In the example workbook, class times are recorded as just the time without date. The goal is to show a schedule of when classes occur over the week.
  1. Select Analysis > Create Calculated Field
  2. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "Start Date"
    2. In the formula field, create a calculation similar to the following:
      DATETIME( "1/1/19 " + [Start Time] )
      Note that there is a space after the date, this will help Tableau Desktop correctly read the date and time.
  3. Repeat steps 1-2 for any fields with time data stored as text values
  4. Right-click and drag [Start Date] onto the Columns shelf
  5. In the Drop Field dialog, select Start Date (Continuous) and click OK
  6. Drag [Day] to the Rows shelf
  7. Create a calculated field with a name like "Class Length" with a calculation similar to the following:
    DATEDIFF('minute', [Start Date],[End Date])/60/24
    Note: elapsed time must be in number of days in order to graph correctly on a Gantt chart. DATEDIFF('day',...) will not return fractional days, therefore we use DATEDIFF('minute',…) and then divide by the appropriate number to get number of days.
  8. Drag [Class Length] to Size on the Marks card
  9. Drag [Class] to Color and to Text on the Marks card

Additional Information

Tableau requires times in Date & Time fields to be associated with a date. This allows for chronological sorting across days.

In order to graph times from different days on top of each other, the times must be shifted to the same day. If the original data does not include a date, then a Date & Time field can be created with an arbitrary date.


Discuss this article... Feedback Forum
Did this article resolve the issue?