2 Replies Latest reply on Oct 14, 2016 8:05 AM by Mark Pesce

    How can I pre format date-time so Tableau will know its a date time?

    Ron Rogers

      hi All,

       

      cant easily figure this out and there's a few convoluted conversion theories/formulas on the forum.  I just want to import the data and visualize; I can easily format my sql or excel data to match any format

      Can someone tell me what format Tableau will know is a date time?  ie mm-yy hh:mm.sss etc, thank you!!!

        • 1. Re: How can I pre format date-time so Tableau will know its a date time?
          Jonathan Drummey

          If you’re directly connecting to a SQL data source (whether server-based or MS Access) then if your data has a date or date time data type then you’ll be all set, no further work is necessary.

           

          For Excel if the field has been formatted as a date like MM/DD/YYYY hh:mm:ss or DD/MM/YYYY (depending on your system locale settings) then Tableau will automatically recognize it as a date or date time data type and no further work as necessary.

           

          If the date field is a text string and you are formatting prior to being connected to in Tableau then using ISO-8601 https://en.wikipedia.org/wiki/ISO_8601 <https://en.wikipedia.org/wiki/ISO_8601> and Tableau should recognize it when you change the field data type to date or date time in Tableau.

           

          If the date field is a number and you are formatting it prior to being connected to Tableau then if you use YYYYMMDD there’s a fast Tableau calculation to turn that into a date in Designing Efficient Workbooks | Tableau Software <http://www.tableau.com/learn/whitepapers/designing-efficient-workbooks> (which is also a great (ok, the best) read on improving Tableau performance). If the field is stored as a number relative to an epoch date (like the UNIX # of seconds since 1 Jan 1970) then you can use Tableau’s  DATEADD() to create that.

           

          Also check out the DATEPARSE() (not available in all data sources), MAKEDATE(), MAKEDATETIME(), DATE(), and DATETIME() functions.

           

          Jonathan

          2 of 2 people found this helpful
          • 2. Re: How can I pre format date-time so Tableau will know its a date time?
            Mark Pesce

            Ron, it really depends on what kind of format your dates and times are currently in. There are lots of functions within Tableau that make it capable to transform data into a date format.

             

            Date Functions

             

            There is also a simple date function as well.

             

            If you're using Tableau 10, it could also potentially recognize your dates already.

             

            Mark