KNOWLEDGE BASE

Transforming Datetime Tick into a Date Time Format using Tableau Calculations


Published: 23 Nov 2020
Last Modified Date: 10 Dec 2020

Question

How to transform a datetime tick such as 635513268009600000 into a Date/Time format like 13/11/2014 18:20:00 using Tableau Desktop calculations.

Environment

Tableau Desktop

Answer

Encase a POWER() expression into a DATETIME() calculation. For example: DATETIME(([<Time Dimension>]* POWER(10, -7) / 60 / 60 / 24)-693593)

To build the view in the attached workbook Time Tic Example_v2019.1, please follow the steps below: 
  1. Create a Calculated Field named Calc1 and enter the following formula: DATETIME(([<Time>]* POWER(10, -7) / 60 / 60 / 24)-693593)
  2. Right click this new calculated field and drag it to four (4) times on Columns, for each field select the following options: 
    1. Discrete YEAR(Calc1)
    2. Discrete QUARTER(Calc1)
    3. Discrete MONTH(Calc1)
    4. Discrete DAY(Calc1)
  3. Right click this new calculated field and drag it on Rows and select SECOND(Calc1)
  4. (Optional) Right click SECOND(Calc1) on Rows and ensure that the following settings match: More > Custom > Date Value 
  5. Right click SECOND(Calc1) on Rows  once more and select Discrete
  6. (Optional) Drag Number of Records to Text
Did this article resolve the issue?