2 Replies Latest reply on Nov 5, 2018 7:17 AM by Ann Engel

    Difference in Job Timestamps

    Ann Engel

      I have a workbook in Excel that had Created_Time, Dispatched_Time, Arrived_Time, and Stop_Time.  These are formatted as "MM/DD/YYY hh:mm:ss".  I then have these calculated fields formatted as "hh:mm:ss":

      Dispatch Time = Dispatched_Time - Created_Time

      Enroute TIme = Arrived_Time - Dispatched_Time

      Arrive Time = Arrived_Time - Created_Time

      Total Job Time = Stop_Time - Created Time

       

      We have a goal timeframe for Dispatch Time (5 minutes) and for Arrive Time (60 minutes), so I would like to be able to provide this information in a report form.

       

      How do I calculate this same data in a Tableau?

       

      Example in Excel:

        • 1. Re: Difference in Job Timestamps
          Julian Yi

          I think that if you're more concerned about the time durations instead of the date, you can extract the times that you've already calculated in Excel and just make some calculated fields in Tableau.

           

          Untitled.png

          This is what Tableau will probably interpret your data as. You can convert your measure into a string and that will leave you with this:Untitled 2.png.

           

          If you wanted to convert that string into a measure that you can graph, then you're going to need to make some calculated fields that convert the string values into an integer value.

           

          Looking at your data, the logic that you're going to want to apply in your calculated field is this:

           

          (INT(LEFT([timestring],1)) * 3600) + (INT(MID([timestring],3,1))*60) + (INT(RIGHT([timestring],2)))

           

          This is assuming that your time values follow this format: h:mm:ss which I think they will. If not, just mess around with the LEFT, MID, and RIGHT functions so that they parse your string data correctly.

          Also replace [timestring] with the name of the field that you want to convert.

           

          This will convert your string data into raw second data. Then you can follow the logic in this post Formatting Time Durations in Tableau | Drawing with Numbers to convert your seconds into hh:mm:ss format. I believe that since your times are not going to exceed 24 hours, you can just divide your seconds by 86400 and change the default number property to Custom: hh:mm:ss and Tableau should take care of the rest.

          1 of 1 people found this helpful
          • 2. Re: Difference in Job Timestamps
            Ann Engel

            Perfect!  It worked!  Thanks so much!