9 Replies Latest reply on Aug 30, 2016 5:09 AM by kettan

    Date "industry time"

    Thorsten Lieder

      Hello community,

       

      I have a table with differnt columns. One of theses columns includes values of "industry time" (number) like

       

      colums Valuethat means...(time values)
      3,003:00
      0,500:30
      1,2501:15

       

      does somebody know, how to change (calculate) these column values in this time values??

       

      Thanks

      T. Lieder

        • 1. Re: Date "industry time"
          Mahfooj Khan

          Try this

          Workbook version 9.3 attached for your reference.

           

          Mahfooj

          1 of 1 people found this helpful
          • 2. Re: Date "industry time"
            Thorsten Lieder

            Hi

             

             

             

             

            • 3. Re: Date "industry time"
              kettan

              You could use numeric calculations like those below and format it as hh:nn:

               

              DATETIME( [industry time] / 24 )


              DATEADD( 'second', INT( [industry time] * 3600 ), #1900-01-01# )

               

              A benefit of using numeric calculations is that they are much faster than string calculations.

               

              It would be nice if numbers could be formatted as duration:   Format number as duration  (34)

              With such a feature, no calculations nor conversions to datetime would be needed!

              • 4. Re: Date "industry time"
                Mahfooj Khan

                As per your sample data values containing comma(",") see the screen shot. That's why I assumed as string and used split. If its simple number field and values are like (3.0 , 0.5 and 1.25) then you should use DATEADD() to get the correct output.

                Using DATEADD('second', INT([Column Value]*3600), #1900-01-01#) and format the field using custom hh:mm hh:nn

                Yeah kettan I know numeric calculation are much faster then string . Thanks for assist.

                 

                Mahfooj

                1 of 1 people found this helpful
                • 5. Re: Date "industry time"
                  Thorsten Lieder

                  Hey kettan and Mahfooj Khan,

                   

                  THANK YOU!!

                   

                  best

                  Thorsten

                  • 6. Re: Date "industry time"
                    kettan

                    As per your sample data values containing comma(",") see the screen shot. That's why I assumed as string and used split. If its simple number field and values are like (3.0 , 0.5 and 1.25) then you should use DATEADD() to get the correct output.

                     

                    Yeah kettan I know numeric calculation are much faster then string . Thanks for assist.

                    In case you missed it, I edited my comment and added an even simpler formula:

                     

                    DATETIME( [industry time] / 24 )

                     

                    As you probably know, Tableau uses a similar (or the same) date system as Excel where 1 equals 1 day and 1/24 equals an hour. With this in mind, I divided [industry time] with 24 and converted it to DATETIME with the DATETIME function.

                     

                    I don't know if this DATETIME calculation performs better than DATEADD, but there are definitely fewer calculations involved, and none of them strings, so with this in mind, it might be reasonable to think that it is faster. It is definitely shorter and easier to read!

                    • 7. Re: Date "industry time"
                      Mahfooj Khan

                      Yeah I've already checked , both are giving the correct result. You rocks!

                      I guess this is more simple and faster. Thanks for another way.

                      • 8. Re: Date "industry time"
                        kettan

                        Thanks, Mahfooj 

                         

                        I wouldn't have been aware of DATETIME() if I hadn't seen it used by  Alex Xu  yesterday in  Re: Date and Time Fields  as an alternative to DATEPARSE() for some date time formats (which, I don't know).

                         

                        I didn't know if it would work or not, but just tried and was a little bit surprised that it actually worked 

                        • 9. Re: Date "industry time"
                          Mahfooj Khan

                          Learned something new today

                          thanks once again!