3 Replies Latest reply on Jun 25, 2018 10:06 PM by Kim McAvoy

    Converting dates

    Jonathan Tunner

      I have a series of days where time is currently listed as; for example 10:00 A.M.  I am trying to simply sort by time of day (in order), but I am having a hard time getting this done.  I am assuming Tableau does not like the format?  How could I use Tableau Prep to change the format to something Tableau likes more?  Or, is there a simple way to do this in Desktop (prefer to do it in Prep if possible).

       

      Thank you!

       

      Jonathan

        • 1. Re: Converting dates
          Joshua Milligan

          Hi Jonathan,

           

          In either Tableau Prep or Tableau Desktop, you can simply change the data type to Date & Time

           

           

           

          Tableau will parse the time correctly and assume a date of 1/1/1900.  The date doesn't matter to you, so in Tableau Desktop, right-click the field and select Default Properties > Date Format

           

          and use a Custom Format string like h:nn:ss AMPM

           

          Now, Tableau will sort the field correctly (because it knows it's a Date + Time), but will only display the time portion based on the default format.

           

          There are a few ideas that you can vote on to let the developers know that a Time data type (as opposed to a Date + Time) would be helpful.  Here's one: https://community.tableau.com/ideas/3315

           

          But the good news is you can accomplish what you want.  And I would suggest that this is a case where Desktop alone probably does everything you need without complicating the source.  But it's nice to know it works the same in Tableau Prep too.

           

          Hope this helps!

          Joshua

          2 of 2 people found this helpful
          • 2. Re: Converting dates
            Jonathan Tunner

            Thank you!!!!!

             

            Jonathan S. Tunner

            Constellation Partners

            411 Libbie Avenue, Suite 2

            Richmond, VA 23226

            804-986-0674

            www.constellationpartners.net<http://www.constellationpartners.net>

            • 3. Re: Converting dates
              Kim McAvoy

              It would be really nice if we could have Tableau Prep be sensitive to date region, like Desktop is.  As far as I know, I have never had to tell Desktop to use the Australian date format, rather than the back-to-front (ahem!) US date, but Tableau Prep constantly confuses me, and I can't find a way to change the default date format.

              Anyone have any clues?  I am constantly confused by whether I am look at mm/dd or dd/mm dates.