4 Replies Latest reply on Aug 27, 2018 4:49 PM by Lionel Rubdi

    Converting Excel [h]:mm:ss to Seconds in Tableau When [h] > 24 Hours

    Christopher Teal

      Hello,

       

      I am using an Excel file to pull of my data into Tableau; One of my fields, Total Time, is in the Excel format [h]:mm:ss and when you bring the Excel file into Tableau, it sets the data type as a string with the same format of h:mm:ss. I am attempting to convert this into seconds to use for another calculation.

       

      I have been successful in converting this field into seconds when the total time is less than 24 hours using the below instructions:

       

      -Convert the Data Type of Total Time to 'Date & time'

       

      -Go to Worksheet, Analysis, Create Calculated Field

       

      -Use Formula:

       

           DATEPART('hour',[Total Time]) * 3600 + DATEPART('minute',[Total Time])*60 + DATEPART('second',[Total Time])

       

      This tends to work beautifully as long as the total time is not greater than 24 hours. Does anyone have any suggestions on how to take into account time being greater than 24 hours?

       

      Thank everyone in advance for their assistance!

        • 1. Re: Converting Excel [h]:mm:ss to Second in Tableau When [h] > 24 Hours
          Christopher Teal

          I believe I have figured this out, but I would like someone to review because I have to believe there is a simpler way than going through what I did.

           

          What I did involved multiple calculate fields, along with a little error correction, but I do hope everyone can follow along. For the example, we will assume that Excel has provided a time of 40:13:10 in the [h]:mm:ss format.

           

          Since Tableau will display total seconds for any period under 24 hours, I needed to get the difference in days and translate that into total seconds; 86400 seconds = 1 Day.

           

          When I converted Total Time to the data type 'Date & time', Tableau displayed it in the Data Source as 1/1/1900 4:13:10 PM. I had to find the  date difference in days from the Tableau's default date of 12/30/1899 12:00:00 AM. I created a calculated field for a reference date, DATE("12/30/1899"). From here I used the below formula for another calculated field called [Days Difference]:

           

          DATEDIFF('day',[Reference Date - 12/30/1899],[Total Time])

           

          This provided a result of 2 days difference; this is incorrect as 40 Hours 13 Min 10 Sec is not 2 days, but according to the data type conversion providing the date of 1/1/1900 4:13:10 PM, it was. To correct for this, I had to use this formula to find the correct days difference:

           

          IF DATEDIFF('day',[Reference Date - 12/30/1899],[Total Time])>=1

          THEN (DATEDIFF('day',[Reference Date - 12/30/1899],[Total Time])-1)

          ELSE DATEDIFF('day',[Reference Date - 12/30/1899],[Total Time])

          END

           

          This provided the result of 1 day; which is correct.

           

          I then used another calculated field to provide the total seconds for the Days Difference, using the below formula called [Days in Total Seconds]:

           

          [Days Difference]*86400

           

          After this, I used a final calculated field to add both [Days in Total Seconds] and portion of time that was less than 1 day, called [Total Seconds First Contact (LESS24 hours)]; this calculated field was simple addition called [Total Seconds] and was the below formula:

           

          [Days in Total Seconds] + [Total Seconds First Contact (LESS24 hours)]

           

          This provided the result of 144790 seconds, which is the correct amount of seconds for 40 Hours 13 Minutes 10 Seconds; this is verified through (40*3600)+(13*60)+10.

           

           

          Somebody please tell me there is an easier way to do this; also, if possible, can someone explain why 40:13:10 in the Excel format of [h]:mm:ss when the data type is changed from 'String' to 'Date & Time' shows 1/1/1900 4:13:10 PM when I believe the default date for Tableau is 12/30/1899 12:00:00 AM?

          1 of 1 people found this helpful
          • 2. Re: Converting Excel [h]:mm:ss to Second in Tableau When [h] > 24 Hours
            Dmitry Chirkov

            I was typing my answer (below) just before this post disappeared but here it is anyway.

            Looks like you are on the right track.

             

            Time only type is not really supported by Tableau but I can offer two workarounds:

            1. Treat column as string, split on ":", get individual fileds for hh/mm/ss and do the math on those
            2. Use Year part of the date: if it's not 12/30/1899 then you are dealing with overflow
              DATEPART('hour',[Total Time]) * 3600 + DATEPART('minute',[Total Time])*60 + DATEPART('second',[Total Time]) + IIF(DATEPART('year',[Total Time]) >= 1900, DATEPART('day',[Total Time])*86400, 0)

             

            See attached workbook for both and hope you don't deal with negative time

             

            p.s. Formula in #2 is not bulletproof as it won't work correctly for times over 31 day

            1 of 1 people found this helpful
            • 3. Re: Converting Excel [h]:mm:ss to Seconds in Tableau When [h] > 24 Hours
              Reza Rahimi

              Here is a different approach when you have a time field as a string in the following format:

              HH: MM: SS

               

              Calculated filed for hours, minutes, and second:

               

              Second=RIGHT([Time Field],2)

               

              Minutes=MID([Time Field],FIND([Time Field],":")+1,2)

               

              Hours=REPLACE([Time Field],RIGHT([Time Field],6),"")

               

              Now you can have the total duration in any desired format, for instance in order to get total time in hours:

               

              Total Duration= ROUND(FLOAT([Hours])+ (FLOAT([Minutes)/60) + (FLOAT(Second)/3600),1)

              • 4. Re: Converting Excel [h]:mm:ss to Seconds in Tableau When [h] > 24 Hours
                Lionel Rubdi

                Dmitry,

                 

                Thanks. This works really good , however there is a very small discrepancy in the final result. I am converting it into hours instead of seconds.

                 

                DATEPART('hour',[Talk]) + DATEPART('minute',[Talk])/60 + DATEPART('second',[Talk])/3600 + IIF(DATEPART('year',[Talk]) >= 1900, DATEPART('day',[Talk])*24, 0)

                 

                Excel: 26:27:28

                Tableau with above calculation: 26.5 hours

                Time to Decimal Calculator : 26.4578 hours

                 

                Any recommendations is greatly appreciated..?

                 

                thanks