6 Replies Latest reply on Jun 29, 2014 2:23 PM by kettan

    How do I make "2008" # format into "2008" date format?

    Katie McCallum

      One of my dimensions is a four digit year (i.e., "2008"). Right now Tableau recognizes it as a number. I need to change the data type to date, but I can't figure out how to get tableau to recognize it as year only. Right now, it interprets the numbers to be part of a long format date and changes "2008" to "02/07/1905". I've tried changing the date format through default properties, but can't find a way for it to show "2008" number as "2008" year.

        • 1. Re: How do I make "2008" # format into "2008" date format?
          Noel Avison

          Well, I'm not sure, but I think you have to have day and month for it to be considered a date. One way you can add this in if you want is to create this calculated field:

           

          DATE( "1/1/"+STR( [Year] ) )

           

          This adds a day and month to your year value and turns it into a date. I don't know if this is helpful for you though. What are you trying to do with the data?

          • 2. Re: How do I make "2008" # format into "2008" date format?
            Mark Holtz

            Katie,

             

            Did Noel Avison's answer solve you problem? The formula he shared will effectively turn a 4-digit year integer value into a date field.

             

            Just for posterity, keep in mind that all dates are really just serial numbers. Computer systems use an "anchor date" to define their "epoch." In essensce, all date values are either x number of days after (positive) or before (negative) that date.

             

            A ubiquitous example is that Microsoft Excel starts with serial date 0 = january 0, 1900, which isn't even a date, but today's date of 6/25/2013 would be 41450, or 1/0/1900 + 41450 days.  Since the ones place indicates days, then decimals will subdivide the day such that 41450.25 would be 6/25/2013 06:00 AM.

             

            Unix systems use 1/1/1970 as the epoch's reference date. This format assigns seconds to the ones place, so 6/25/2013 is actually 1372136400.

             

            I imagine that's more than you wanted to know, but in essence, just trying to turn 2008 into a date will add 2008 units (days in Tableau) to the epoch reference date. That's why you get a funky date in 1905...

            • 3. Re: How do I make "2008" # format into "2008" date format?
              Alex Kerin

              The only caveat I would add to this great explanation is that the epoch used by Tableau (in extracts) and other databases is different from Excel and can lead to a subtle, but sometimes critical, difference of 2 days.

              • 4. Re: How do I make "2008" # format into "2008" date format?
                Kevin Herbert

                To take the question a little further... when you have a dimensional model your time dimension has year, month, day, and yearmo all as integers and they are indexed.   Usually you want yearmonth on your viz.   the problem with using the date as a whole is that it gets interpreted in the sql request to the data source thus rendering the indexes mute.   It would be nice if you could link the date parts to the date so that the generated sql could use them.   Trying to use the date parts themselves in the viz doesn't work.   unless there is a way to create a date from the parts and tableau recognizes the parts and uses them.      Just a thought on dates.

                • 5. Re: How do I make "2008" # format into "2008" date format?
                  michael.bettersworth

                  Wasn't 8.2 supposed to be able to more easily deal with four digit years and dates in general to avoid the 1905 issue? I ran into this again and would like to avoid using a calculated field every time I have a four digit year in an Excel file--which is often.

                  • 6. Re: How do I make "2008" # format into "2008" date format?
                    kettan

                    For random readers of this thread, I would like to share a numeric calculation, because such usually (always?) perform better than textual calculations. That said, I haven't tested if this formula performs better than the other:

                     

                    dateadd('year', [Year]-1980, #01-01-1980#)