1 Reply Latest reply on Mar 3, 2016 8:43 AM by Paul Sherras

    Date format error

    Toby Close

      Hi all,

       

      I have imported a large set of data from Excel that include birth years for each entry.

       

      The birth years are currently formatted as a whole number, and vary in format between:

       

      1956

      86 (as in 86 A.D.)

      -400

       

      Tableau won't recognise these as dates and changes them all to years between 1899 and 1909. 

       

      I can use DATE("01/01/" + STR([Birthyear])) to recognise dates between 100 and 1016 correctly, but any years before that are either null or have 19 ahead of them (e..g 86 goes to 1986).

       

      I am new to Tableau but haven't found an answer to this problem despite searching.

       

      Thanks in advance!

        • 1. Re: Date format error
          Paul Sherras

          Hi Toby,

           

          Looking at the way Tableau formats dates, anything pre 1900 is coming through as - number and turn to nulls at 400AD.

           

          I got the numbers a little bit wrong, apparently there are more leap years than one in four (who knew?!), but please find attached a workbook reviewing this:

           

          With this in mind, I'm not sure whether tableau is currently able to do what you would lke

           

          Kind regards,

           

          Paul