6 Replies Latest reply on Jan 26, 2012 5:13 PM by Aaron Rubinstein

    Format time difference as total hours and minutes

    Aaron Rubinstein

      I thought that Tableau custom number formats used the same format codes as Excel, however it does not appear that Excel's elapsed time codes work in Tableau. See the attached image file; the last three codes at the bottom of the table are what I am referring to.

       

      Here is what I am trying to do: In the attached workbook I have two datetime values, "Submitted" and "Worked". I added a simple calculation for Elapsed Time: [Worked] - [Submitted].

       

      This displays the difference between the two datetime values as a number representing days (with decimal values representing fractions of a day). I would like to format this Elapsed Time dimension to display as total hours and minutes. In Excel I would just apply the following custom format: [h]:mm, however Tableau does not seem to accept that. Tableau will accept h:mm, but that will not correctly display elapsed time over 24 hours.

       

      For example, the first row shows submitted 1/1/2012 1:09:38 PM and worked 1/3/2012 7:53:05 AM. I would like the elapsed time to display as 42:43 (42 hours and 43 minutes), however formatting the dimension as h:mm results in 18:43.

       

      Alternatively, if there was a way to format as days:hours:minutes that would also work (the example above would then be 1:18:43 for 1 day, 18 hours, 43 minutes). When I try to apply the custom format d:hh:mm I get 31:18:43... not sure where that comes from?