2 Replies Latest reply on Jun 13, 2016 10:25 PM by Mahfooj Khan

    Format Date/Time

    Morgan Strong

      Hi,

       

      I am trying to format some data in a visualization as hh:mm:ss, however it doesn't seem to be formatting correctly.  I am parsing two date fields, as they are currently formatted differently and am using DATEDIFF function with seconds. The calculation that I am using for my dashboard is DATETIME([Date Field]/86400), and my dashboard is returning a different value than it should. I read that this may not work for values over 24 hours? Is there an alternative solution for this?

       

      Thanks!

      Morgan

        • 1. Re: Format Date/Time
          Tom W

          Hi Morgan,

          We'd really need to see an example of the data you're working with. At the very least,  an example of the problem you're encountering.

           

          Please attach a Tableau Packaged Workbook including some sample data and your calculations.

          • 2. Re: Format Date/Time
            Mahfooj Khan

            As Tom has said It'll be more helpful for us If you share the sample workbook.

            However you may try this.

            First get the datediff in seconds.

            Time in Second:  DATEDIFF('second', [1st Date Field],[2nd Date Field])

            To get the hh:mm:ss format use below mentioned formula.

            STR(INT(SUM([Time in Second]3600))

            + ":" +

            IF INT(SUM([Time in Second])%3600/60)

            < 10 THEN "0" ELSE "" END + STR(INT(SUM([Time in Second])%3600/60))

            + ":" +

            IF INT(SUM([Time in Second]) %3600 %60)

            < 10 THEN "0" ELSE "" END + STR(INT(SUM([Time in Second])%3600 %60))

            Mahfooj