2 Replies Latest reply on Sep 7, 2016 6:04 PM by kettan

    CSV Serial Date convert to date in Tableau

    Bryan Haynie

      Hello,

       

      I'm using 9.3 to stack .csv files together to build dashboards but I need to use the dates from my excel files to show changes over time.  The issue I'm having is when I bring in my dates they appear as a 5 digit number which I understand is a serial date.  I have tried several calculated fields that I've found via google to convert these values to usable dates but to no avail.  (This is also after converting the values to string)  I either end up with nulls or other unusable values. 

       

      Does anyone have a method/formula that would solve this issue?  Example: date 12/23/2014 shows as 41996 in csv but I need a way to convert this to a usable date after I've brought the CSV files into Tableau.

       

      Thank You

        • 1. Re: CSV Serial Date convert to date in Tableau
          Tom W

          As per - DATEVALUE function - Office Support

          Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.

           

          Here's a good document which outlines how to convert the serial number in other systems - Excel Date Conversion (Days from 1900) | Kirix Strata Blog

           

          In Tableau, we use the DATEADD function as follows - dateadd('day',[Dateval]-2,#1900-1-1#)

          2 of 2 people found this helpful
          • 2. Re: CSV Serial Date convert to date in Tableau
            kettan

            As per - DATEVALUE function - Office Support

            Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.

            In Tableau, we can use the function DATE() or DATETIME() to convert such numbers to dates:

             

            DATE( 1 )     returns January 2, 1900    ( 1 more day )

            DATE( 39448 ) returns January 3, 2008    ( 2 more days because of leap year error in Excel )

             

            With this in mind, I think you could use this formula:

             

            DATE( [Excel Date Number Field] - 2 )

             

            But of course, check if the result returns what is expected.

             

            Ps. This document isn't about this question, but shared to show how DATETIME() can be used with numeric calculations:  DATETIME() for converting numbers to time