clock image from http://images.cdn.fotopedia.com/flickr-4750765479-original.jpg

Formatting Time Durations in Tableau

This post was updated on 24 July 2015 to include additional details on applying the custom formatting calculation to aggregates.

Here’s a quick lunchtime post on working with durations in Tableau. By duration, I mean having a result that is showing the number of seconds, minutes, hours, and/or days in the form of dd:hh:mm:ss. This isn’t quite a built-in option, there are a several ways to go about this:

  • Use any duration formatting that is supported in your data source, for example by pre-computing values or using a RAWSQL function.
  • Do a bunch of calculations and string manipulations to get the date to set up. I prefer to avoid these mainly because they can be over 1000x slower than numeric manipulations. If you want to see how to do this, there’s a good example on this Idea for Additional Date Time Number Formats. (If that idea is implemented and marked as Released, then you can ignore this post!)
  • If the duration is less than 24 hours (86400 seconds), then you can use Tableau’s built-in date formatting. I’ll show how to do this here.
  • Do some calculations and then use Tableau’s built-in number formatting. This is the brand-new solution and involves a bit of indirection.

Durations Guaranteed To Be Less Than 24 Hours: Tableau’s Date Formatting

If you know your duration is always going to be less than 24 hours (86400 seconds), , then you can take advantage of Tableau’s built-in date formatting. The first step is to get your duration in seconds, if it’s not already. For example, for looking at the duration between two dates you can use DATEDIFF(‘second’,[Start Time], [End Time]). Once you have that number of seconds, then you can use the following calculation

If the total time is less than 24 hours, then we can use the following calculation: DATETIME([Seconds]/86400). Then we can use Tableau’s date format to set the format as hh:nn:ss (in this case hh:mm:ss will also work):

2014-07-30 13_32_56-Tableau - duration formatting

Here’s a view…take a look at what happens at 86400 seconds and beyond:

2014-07-30 13_27_39-Microsoft PowerPoint - [Presentation1]

Instead of showing 24 hours, the date formatting looks just at the hours, minutes, and seconds. As long as your duration is less than 86400 seconds, everything will be simple using this technique.

Any Duration: Using Arithmetic and Number Formatting

Though Tableau’s built-in formatting is not as powerful as Excel, we can still do quite a bit. Check out Robert Mundigl’s Custom Number Formats for a pretty exhaustive list of what can be done. (He’s also got a fantastic post on String Calculations that is worth checking out.)

For example, we can set up a custom number format of 00:00, and that will perfectly format a number as a time…up to 59 seconds, that is:

2014-07-30 13_42_26-Tableau - duration formatting

 

However, this is a clue to the next step, which is that since we space out numbers by colons, all we need to do is get the right value into the right decimal place. So, for example, instead of 60 seconds (1 minute) being a value of 60, it has to have a value of 100 so the 00:00 formatting will make it 01:00. Here’s a formula that does just that for mm:ss values:

//replace [Seconds] with whatever field has the number of seconds in it
//and use a custom number format of 00:00:00:00 
//(drop the first 0 to get rid of leading 0's for minutes)
IIF([Seconds] % 60 == 60,0,[Seconds] % 60)// seconds
+ INT([Seconds]/60) * 100 //minutes
In this formula, [Seconds] is a record-level field. In order for the calculation to work accurately in some situations (such as using it with Subtotals and Grand Totals) you’ll need to change [Seconds] to SUM([Seconds]) or some already-aggregated calculated field. –added 2014-12-22 per notes in the comments.

The formula uses the % (modulo) function to divide the number of seconds by 60 to get the remainder, the IIF statement is there to deal with what happens when the number of seconds is divisible by 60, the result of that is the number of seconds, then the number of minutes *100 is added to that. Here’s a view:

2014-07-30 13_46_12-Tableau - duration formatting

 

For hh:mm:ss the formula gets a little more complex. Just as the seconds had to be transformed to make 0-59 seconds, minutes have to do a similar transformation:

//replace [Seconds] with whatever field has the number of seconds in it
//and use a custom number format of 00:00:00 (drop the first 0 to get rid of leading 0's for hours)
IIF([Seconds] % 60 == 60,0,[Seconds] % 60)// seconds
+ IIF(INT([Seconds]/60) %60 == 60, 0, INT([Seconds]/60) %60) * 100 //minutes
+ INT([Seconds]/3600) * 10000 //hours

And for dd:hh:mm:ss there’s yet another transformation to convert hours to 0-23:

//replace [Seconds] with whatever field has the number of seconds in it
//and use a custom number format of 00:00:00:00 (drop the first 0 to get rid of leading 0's for days)
IIF([Seconds] % 60 == 60,0,[Seconds] % 60)// seconds
+ IIF(INT([Seconds]/60) %60 == 60, 0, INT([Seconds]/60) %60) * 100 //minutes
+ IIF(INT([Seconds]/3600) % 24 == 0, 0, INT([Seconds]/3600) % 24) * 10000 //hours
+ INT([Seconds]/86400) * 1000000 // days

Here’s a view showing all three calculations:

2014-07-30 13_53_37-Tableau - duration formatting

A little dose of math, sprinkle some custom number formatting on it, and voila, there’s some usable duration formatting. If you wanted to keep going into weeks then there would need to be another level of calculation, to get into months and years I’d probably use a different approach because the intervals (month lengths and year lengths) aren’t fixed.

Here’s the workbook on Tableau Public: Duration Formatting

If this is useful to you, or you have an alternative technique, let me know in the comments below!

Addendum on Aggregation (July 2015)

Some people people using this technique have run into problems as seen in the comments below and on the Tableau forums. For example, if we build a view like this and sum up all the values of Seconds with the duration calc applied (see the SUM() aggregation) the numbers don’t add up. In this view the total time in mm:ss should be 44558:00 but it’s showing up as 44554:80, a non-sensical amount:

Alternatively, if we keep Seconds in the view as a dimension but add a Grand Total, the Grand Total isn’t adding up either:

The issue is with regards to order of operations. When we build the views with the grand total or by summing the measure, there’s an aggregation happening and it’s not happening at the right time vis-a-vis the formatting. I put together a series of graphics to explain what is going on.

In the original post the duration calculations are performed for each record and the results are treturned from the view. So it looks like this:

So in this case here’s what’s going on under the hood:

That’s not often used in practice, more often a view looks like this, where the mm:ss is being used as a measure and then when the original calculation is brought into the view we can see that Tableau is actually aggregating the duration calculation with the default aggregation of SUM():

However, that SUM() has no effect because the level of detail (the granularity) of the view is the same is the same as the data — in other words, the view is effectively displaying the raw data. So the order of operations is actually like this:

So even though these two views look exactly the same, under the hood they are using two different ways to get there. Where this causes problems is when we apply an aggregation that is across multiple records or in a grand total or subtotal. The duration calculation that was meant to be applied to the final result is being applied to every record and it’s that transmogrified amount that is getting summed:

The solution is pretty straightforward, we just need to do the aggregation *before* we apply the duration calculation. So instead of the [Seconds] of the original calculation we use Sum Seconds with the formula SUM([Seconds]), and the formula for mm:ss (agg) is:

//replace [Sum Seconds] with whatever field has the number of seconds in it
IIF([Sum Seconds] % 60 == 60,0,[Sum Seconds] % 60)// seconds
+ INT([Sum Seconds]/60) * 100 //minutes

So the order of operations is now:

And here’s a view showing the inaccurate and now accurate aggregate calculations:

And a grand total:

I’ve updated the Duration Formatting workbook to reflect this addendum.

77 thoughts on “Formatting Time Durations in Tableau

  1. Dave Walp

    Wow! I know I will get a lot of use out of this. Thank you for your massive contributions to the community.

    Reply
  2. Bobby Weaver

    Great information. I’ve already pasted the posting into Evernote for safe keeping. The only tidbit I would add is that your % (modulo) function formula also supports the hh:mm format as long as you specify ‘minute’ in the DATEDIFF formula.

    Reply
  3. Pingback: Time Duration in Tableau | Data Knight Rises

  4. Bryan Andrews

    I have used the formula with success in calculating total Utilization hours for our ambulance units. Now the manager wants the format of the number to change. Basically he wants to see a whole number for the hours and the minutes and seconds shown as a decimal. In other words 198:22:15 becomes 198.37. Can I modify the formula to get that alternate format?

    Reply
  5. Sabree

    I wanted to get your thoughts and advice on how to handle “drive times” from certain zip codes in Tableau. I’ve been searching around for some examples of this and haven’t found any yet. Thanks

    Reply
  6. Hrvoje

    Thank you for the post Jerremy. But when I tried to aggregate values I had something like 23:80:10. To seconds are more than sixty. Is there any fix for that ?

    Reply
    1. Jonathan Drummey Post author

      Hi Hrvoje,

      I’m Jonathan, not Jerremy. 🙂 Without seeing your calc, I can’t help you. Try posting a packaged workbook with some sample data to the Tableau forums, then link back to it here and I’ll take a look.

      Jonathan

      Reply
  7. Pingback: Tinkering with FLOAT() and Durations « Tableau Tinkering

  8. Aleksandar

    Hello everyone,

    I’ve tried:

    For hh:mm:ss the formula gets a little more complex. Just as the seconds had to be transformed to make 0-59 seconds, minutes have to do a similar transformation:

    //replace [Seconds] with whatever field has the number of seconds in it
    //and use a custom number format of 00:00:00 (drop the first 0 to get rid of leading 0’s for hours)
    IIF([Seconds] % 60 == 60,0,[Seconds] % 60)// seconds
    + IIF(INT([Seconds]/60) %60 == 60, 0, INT([Seconds]/60) %60) * 100 //minutes
    + INT([Seconds]/3600) * 10000 //hours

    But as result I get, for example 11:98:56….
    I don’t no what I did wrong.

    If anyone knows what is the problem please post it.

    Aleksandar

    Reply
    1. Jonathan Drummey Post author

      Hi Aleksandar, is that the exact calculation you are using in your workbook? It looks like you copied and pasted the text from the blog post, so I can’t be sure. Also, are you looking at a value in a detail row or a Grand Total? Grand Totals introduce an additional complication where you have to use an aggregate measure for the seconds in place of a record-level of seconds. What would make it easiest for me to help you is if you posted a link to either a Tableau Public viz or a Tableau packaged workbook file that the calc and some sample data.

      Jonathan

      Reply
  9. Aleksandar

    Hi Jonathan,

    I’ve copied an pasted the text and replace [Seconds] with my measure [total_talk_time] which is in seconds (ex.15456).
    I have a thousands of agents with different talk time and I want to see total of talk time in one month per agent but in format hh:mm:ss(but I have examples where there is overlap of 24 hours). I am very familiar with tableau but I dont exactly understand this calculation and therefore i dont know if i need to change something more.

    Thank you.
    Aleksandar

    Reply
    1. Jonathan Drummey Post author

      This is a follow-up, Aleksandar and I had a conversation over email. The solution turned out to be using an aggregate calculation in place of [Seconds] in the calculation, I’ve added a note to that effect above.

      Reply
      1. chirag

        Hi Jonathan, I have same issue with the conversion of seconds to time format.. when use dd:hh:mm:ss and if value is greater then 86400 its works ok but for values less then 86400 it turns out as 30:04:39:19. how to resolved it. I tried to go through our block but no luck.

        Please let me know what I am doing wrong, as my data is mix of values which are greater then 86400 or less than 86400

        Reply
        1. Jonathan Drummey Post author

          Hi Chirag,

          As the post above describes Tableau’s build in date formatting fails when the number of seconds is greater than 86400. Please read the entire post and download the packaged workbook to see how to use custom number formatting to enable you to get the desired results.

          Reply
  10. Pingback: Tableau Tips,Tricks,Best Practices - Formatting - Jenny (Xiao) Zhang

  11. ashok

    Hi Jonathan,

    This is super. But my scenario requires me to aggregate the time and it does not work with that. if i sum it up this gives me values like 01:70: 32:79. Please explain if there is any way to overcome this issue.

    Reply
  12. Rody Zakovich

    Hello Jonathan,

    Thank you for pointing this out in the Forum. This is a fantastic workaround to a problem that I did not think was possible (Showing Duration in Measure Values)

    Love your blog and everything you do for the community!

    Regards,
    Rody

    Reply
    1. Jonathan Drummey Post author

      Hi Erik,

      Please see the https://public.tableau.com/views/durationformatting/DurationstotheMinute?:embed=y&:display_count=yes&:showTabs=y worksheet in the Duration Formatting workbook. If you’re starting with seconds you have to make a choice about rounding seconds to minutes, the worksheet has calculations for both. The worksheet shows hh:mm calcs, to change them to h:mm all you need to do is use a custom format of #:##. Let me know if you have any more questions!

      Jonathan

      Reply
      1. Anita

        Hi ,

        I have a date field which give total duration spent by a person on particular task. The hour field has values lik , ” 0:45 : 34″ , ” 99:30:00″ , “203:56:45” etc. When I drag the filed to tableau , it shows data with date which converts the hours to below 23 number.

        I want to extract the total hours for a participant in hours. How Can I do that ?

        Say for “99:30:00” = 99.5 hours.

        Thanks,
        Anita.

        Reply
        1. Jonathan Drummey Post author

          Hi Anita,

          Since Tableau doesn’t have a built-in notion of duration it automatically interprets those fields as dates as you’ve discovered. In order for to convert the h:mm:ss duration into a decimal number of hours we need to do some string manipulation. Here’s a formula that works on most data sources using the FINDNTH() function introduced in Tableau v9. If you are on a data source that doesn’t support FINDNTH() that could be replaced with a combination of LEFT() and LEN(). There’s likely a regex-based solution as well.

          //assumes a duration string of format h:mm:ss where 34 seconds is 0:00:34 return the decimal number of hours
          //hours
          INT(LEFT([myField], FIND([myField],’:’)-1))
          // minutes turned into hours
          + INT(MID([myField], FIND([myField],’:’)+1, FINDNTH([myField],’:’,2) – FIND([myField],’:’) – 1)) / 60
          // seconds turned into hours
          + INT(RIGHT([myField], 2)) / 3600

          Jonathan

          Reply
          1. Anita

            Thank you Jonathan. The actual problem with my data was Tableau converting the hours >24 to days automatically, that’s why I was not able to get the correct Hours from Date field by datepart function. Now I have converted the days also to hours to get correct total hours.

  13. mdeutschmtl

    Another hat tip from up on Montreal… I had to depict large numbers of elapsed seconds as ‘hh:mm:ss’ and found this very clear and well explained. Thanks, Jonathan! 🙂

    Reply
  14. Don Wise

    Hi Jonathan,
    Thank you for the work here; it’s great! I think it’s resolving my issue with supplying a “dd” format to my data, but I’m seeing an issue with my results. We’re using Tableau to produce monthly reports for our local Fire Departments.

    Normally we only deal with incident durations of less than 24 hours so our results have never been in question. However, now we’re in need of determining an ambulance and fire engine total duration compared to the number of incidents they respond on each month. So we’re entering into a need to compute in a “dd:hh:mm:ss” format.

    For the majority of the units incident duration being measured, it looks good. But two of them that have less than 100 incidents per month are resulting in the following result(s): Engine 38 = 31:00:35:42 and Engine 39 = 30:19:48:28

    I’ve tried removing “/86400” from my original Incident Duration calc in order to use just the resulting record level decimal number and then used your formula separately for formatting, but achieved even stranger results. Is it possible our date formats that we’re using are differently structured?

    Thoughts? And thanks in advance for any insight…Don

    Reply
    1. Jonathan Drummey Post author

      Hi semaozJorge, this totally depends on how your data is set up. Is there one record with a duration value, are there multiple records that each have a start & end time, are there 2x that number of records where each record has an employee ID and a start time or an employee ID and an end time? How much of the duration calculation we have to do in Tableau has a huge impact on what we do for the filter calculation.

      Jonathan

      Reply
  15. MIH

    Hi Jonathan,

    This is fantastic, thank you! I was struggling with the nonsensical outcomes when subtracting time durations for a year over year comparison, and the Sum(seconds) substitution worked like a charm.

    My only problem now is for each of the two years I’m comparing, I’m only able to aggregate as a sum in the grand total column, when I would want to see an average instead. To further explain, I have one row of data with average time durations for each month last year, and another row with average durations for each month this year. My third row subtracts last year from this year’s duration each month to provide the variance. I want the grand total column in the end to give me the average for each year, and the variance between the two, but it will now only add all months for each year.

    Any idea how to get around this issue? Thanks!

    Reply
    1. Jonathan Drummey Post author

      Hi,

      Without seeing exactly how you constructed the view I can’t say, you could send me screenshot or a Tableau packaged workbook at jonathan(dot)drummey(at)gmail and I can take a look.

      Jonathan

      Reply
  16. Christopher McKinnish

    I’m finding that this does not work for Table calculations.

    I have a table of date-times, where I’m using a LOOKUP to find the duration from one to another.
    Below is my data set. The “Time Between Dispatches” is a Tableau Table Calculation.

    I have the same issue. When the result goes beyond or equal to 24 hours, I get the wrong result. You see this on the very last row where the value should be 25:37 hours. I can use Tableau’s date format to show dd:hh:mm, but this results a result of 31:25:37, and plates a 30 in front of the other values.

    Employee Number Dispatch Number Dispatch Date-Time Time Between Dispatches
    10013 4258813 10/3/2016 21:58
    10013 4260518 10/4/2016 04:04 06:06
    10013 4268051 10/4/2016 21:57 17:53
    10013 4269793 10/5/2016 04:01 06:04

    10200 4258384 10/3/2016 20:36
    10200 4259314 10/4/2016 00:01 03:25
    10200 4260763 10/4/2016 05:01 05:00
    10200 4267685 10/4/2016 20:40 15:39
    10200 4269068 10/5/2016 01:43 05:03
    10200 4269385 10/5/2016 02:43 01:00

    10203 4258222 10/3/2016 20:10
    10203 4259009 10/3/2016 22:46 02:36
    10203 4259949 10/4/2016 02:12 03:26
    10203 4267034 10/4/2016 19:01 16:49
    10203 4267975 10/4/2016 21:42 02:41

    10219 4254291 10/3/2016 08:33
    10219 4263621 10/4/2016 10:10 01:37

    Below is the table formula for previous row duration determination

    (FLOAT(DateDiff(‘second’, Lookup(MIN([Dispatch Date-Time]), -1), Lookup(MIN([Dispatch Date-Time]), 0)))/86400)

    Any suggestions?

    Reply
    1. Jonathan Drummey Post author

      Hi Christopher, this isn’t an issue with table calculations, it’s an issue with Tableau’s formatting – see the “Durations Guaranteed To Be Less Than 24 Hours: Tableau’s Date Formatting” above for more info. The summary is that we *can’t* currently use Tableau’s number formatting to show a duration >24 hours. That’s why a calculation like the ones I wrote in the rest of the post is necessary. I suggest that you just return the DateDiff(‘second’, Lookup(MIN([Dispatch Date-Time]), -1), Lookup(MIN([Dispatch Date-Time]), 0)) as [Seconds] and then use that inside the calculations I described in the post. If you have questions from there let me know!

      Jonathan

      Reply
      1. Christopher McKinnish

        Thank you. You are 100% right on both counts. I think I was having a brain aneurysm from being so frustrated with Tableau over what should be so @#$ simple. I apologize for wasting your time, and thank you so very much for such a quick response!!!.

        Reply
  17. Pingback: Business Hours calculation in Tableau – meowBi

  18. Pingback: Dashboard Week Day 4 - What has Coach Andy accomplished in 286 runs? - The Data School

  19. Pingback: A Look at Andy Kriebel's 286 Runs - The Data School

  20. Pingback: Dash-Board - The Data School

  21. Pingback: The Dreaded Dashboard Week Series: What Has Coach Andy Accomplished in his 286 runs? - Run Keeper Data - The Data School

  22. Pingback: Workout Wednesday Week 21 – Part 1 (My approach to existing structure) – Jackson Two

  23. maria ashraf

    Hi Jonathan,

    I am probably too late to the party and not sure if you are still entertaining questions on this topic but would really appreciate if you are.

    First thing first, thank you so much for the detailed explanation and step by step calculation. This is very very helpful.

    I am creating a call center reps scorecard to see total staffed time, time spent on calls, on breaks etc.and your method of aggregation and duration calculation is working absolutely fine but when I am trying to calculate a average line for the entire table, it is not formatting the time properly 🙁 Any tips how to do that?

    Reply
    1. Jonathan Drummey Post author

      Hi Maria,

      Without seeing your view, the calculation, and the data I can’t be sure, so I’m guessing here: I’m thinking that you’ve created a calculation that gets the desired duration result at the detail level, and then you’re using a grand total or average reference line and that is breaking. If that’s the case then what you’ll need to do is either use a customized grand total or custom calculation for the reference line. The reason why is in two parts: 1) the method I outlined here is ‘hacking’ Tableau’s number formatting to make a number look like a date. 2) things like grand totals and average reverence lines are additional aggregations at separate levels of detail so we need to make sure those computations are returning the desired results *before* we do the number formatting to make it look like a duration.

      If that’s not enough to help you then please post a thread on the Tableau forums http://community.tableau.com with a packaged workbook with some sample data and your work so far and then reply to this thread and I’ll take a look.

      Jonathan

      Reply
  24. 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)

    Reply
    1. Jonathan Drummey Post author

      Hi Reza,

      I’d avoid doing this for performance reasons. The set of calculations you suggest is using three sets of string manipulations and then the Total Duration is doing three string to number conversions, these are really expensive operations compared to manipulating numbers. My preference is to get the date string to a number as fast as possible and then manipulating that is manipulation a number.

      In this case if the data source supports DATEPARSE() then the single calculation FLOAT(DATEPARSE(‘hh:mm:ss’, [Time Field]))*24 will work. The inner DATEPARSE() does a single string conversion to a date time and then the FLOAT() does a number conversion that can be multiplied by 24 to get the decimal number of hours. If the data source doesn’t support DATEPARSE() then we can get the decimal hours with either FLOAT(DATETIME(‘1900-01-01 ‘ + [Time Field]))*24 or FLOAT(DATETIME(‘1899-12-30 ‘ + [Time Field]))*24, the latter will be necessary for some Excel sources.

      Reply
  25. Pingback: Week 38: A Day at the Races | Makeover Monday

  26. Sarah

    Hi Jonathan,

    This calculation is super helpful! Thank you for the post. I am just wondering if it is possible to somehow still show zeros even if there aren’t any hours or minutes. For example, if I have something that is only 33 seconds, it will show as ::33 instead of 00:00:33. I formatted my calculation to be ##:##:##, but when there is no data, it doesn’t show the zeros. I tried to add an Ifnull put zero, but it didn’t work. Any ideas on how to make the zeros show?

    Reply
      1. Sam

        Hi Jonathan,

        No they weren’t helpful. I tried all the suggestions. I would appreciate it if you had some time to spare on it.

        Reply
  27. Julian Yi

    Hi Jonathan,

    I’ve noticed that if you were to use this method to graph all of your time durations, the axes don’t seem to follow the same logic. Is there a way to fix all of that?

    Thanks,

    Julian Yi

    Reply
  28. Julian

    Hi Jonathan,

    I am having trouble formatting the tick marks along my axis to follow the logic that you’ve set up with the time durations. The minutes sections extend past 60 minutes and instead of displaying 01:20:00 in hh:mm:ss, it displays 00:80:00. Are there any possible solutions to this problem?

    Thanks

    Julian

    Reply
    1. Jonathan Drummey Post author

      Hi Julian,

      I’m sorry the post isn’t more clear. This is just about formatting durations for display purposes (like on Label, Text, or Tooltips), or potentially used as a discrete header (blue pill on Rows or Columns), not for axes (green pill on Rows or Columns). Tableau’s axes are laid out as numbers, dates, or datetimes, depending on the data type. Since the method I described here is hacking a field with a number data type to be formatted as a date when we use it as a continuous pill on Rows or Columns Tableau is seeing the underlying number data type and using that to lay out the axis. My suggestion is to a) use a date or datetime data type as the axis and then play with the date formatting so it’s “good enough” (and then use tooltips for exact values) or b) just use the raw seconds value as your axis and then hide the axis and just use tooltips.

      Reply
      1. Julian Yi

        Hi Jonathan,

        Sorry for commenting twice on your page, I thought that the first comment didn’t go through. I may have to go with option b because tableau is scaling things in a very odd manner.

        Thank you so much for the help.

        Reply
  29. Kenneth Matthews

    Hi Jonathan,

    I am having difficulty aggregating time duration across several calculated fields. I am trying to create a field called “Phone Assistance Time,” which is dependent on 4 other calculated fields in the workbook. I used the formula for hh:mm:ss and modified it using the SUM function as suggested.

    The formula works when viewing the fields independently, however when they are summed together for the “Phone Assistance Time” field (which will be displayed on the dashboard), I get values like 01:84:22 which doesn’t really make sense since I want it to be hh:mm:ss. The grand total is not displaying correctly either.

    The original data source for this file are two custom SQL statements that I have blended together, with LOGIN and EST as the join keys. However in this example I’ve just used two Excel spreadsheets to mimic the data so I can attach as a packaged workbook that is accessible.

    The packaged workbook can be found here:
    https://community.tableau.com/message/909477#909477

    Thank you,

    Kenny

    Reply
  30. Karina

    Hi Jonathan, I am using the formula below in order to show Avg Speed to Answer in time format in Tableau and it is working perfectly in the dashboard since I use the format hh:mm:ss but when I export the raw data in a .csv file, the value lost its format. For instance, if it is 00:01:50, I mean, 1 min and 50 seconds in time format in the dashaboard, the file in excel shows 150 so it is not something accurate for our stakeholders, what do you suggest I can do in order to show it properly? Thanks in advance.

    Reply
  31. Kavi

    Hi Jonathan,

    I have an issue while showing Median and Average in bar chart Reference Line.
    It is showing 00:39:83 where it is expected 00:40:23 for Median.
    It is showing 01:68:27 where it is expected 02:00:15 for Average.

    could you please where i am going wrong?

    FYI.. TimeToAck_secs is already aggregated.

    Here is the formula which i used.

    IIF([TimeToAck_secs] % 60 == 60,0,[TimeToAck_secs] % 60)
    +IIF(INT([TimeToAck_secs]/60) % 60 == 60,0,INT([TimeToAck_secs]/60) % 60) * 100
    +IIF(INT([TimeToAck_secs]/3600) % 24 == 0,0, INT([TimeToAck_secs]/3600) % 24) * 10000
    +INT([TimeToAck_secs)]/86400) * 1000000

    Regards,
    Kavi

    Reply
    1. Jonathan Drummey Post author

      Hi Kavi,

      You’re sharing a number format of 00:40:23 i.e. hh:mm:ss but using the formula for dd:hh:mm:ss, I’m not sure if that might be the problem.

      Try copying & pasting the original formula for hh:mm:ss from the post and replacing [Seconds] with your [TimeToActk_secs].

      Jonathan

      Reply
      1. Kavi

        Thanks Jonathan, i had tried by changing format into dd:hh:mm:ss but no luck.

        Let me explain what exactly i am doing.
        1. Time To Ack in Secs —> aggregated by Sum
        2. Am finding Median(Time To Ack in Secs) —> Business want to see Median time for each approver
        3. Am converting above median time to ack in secs into dd:hh:mm:ss format by using your formula
        4. Created bar chart , Approver in x-axis and Median time to ack in ddhhmmss in y-axis
        5. Sorted y-axis low to high and trying to find overall Median time by adding reference line , now the reference line median output shows 00:00:39:83.

        When i created windows_median add into dual axis , the result shows as expected.

        I suspecting , median over median causing this issue.

        Thanks,
        Kavi

        Reply
  32. Daniel Alonso

    First of all thank you very much for your contribution, I haven’t seen any other source providing better understanding and info about this issue in Tableau.

    I am having trouble when showing a Measure as dd:hh:mm:ss. The value itself is good but when I show totals as average, it display more than 60 in hours or minutes or other fields. Have you got any solution to this?

    I see you use this calculations as dimensions and sadly that does not fit my needs.

    Thank you in advance!

    Dani

    Reply
    1. Jonathan Drummey Post author

      Hi, the “time” formatting is being applied to a specially constructed number. Tableau’s total using functionality is performing a secondary aggregation on the value, which in this case is the specially constructed number and not the original value. My suggestion would be to use an INCLUDE Level of Detail expression of the form AVG({INCLUDE [the dimensions in your view] : [the original measure]}) and then format that, then use the default totals.

      Reply
  33. Dom

    Hi, first of all a big thank you for sharing this. I have used your 00:00:00:00 format for time durations that exceed 24h. When shown in a bar chart, this works great up until 00:23:59:59. Once we jump to 01:00:00:00 on the bar chart, the bar more than triples in size because on the y-axis, there are incorrect “time durations” between 00:23:59:59 to 00:99:99:99 (which, as a time expressed in hours, obviously does not make sense because it shows 99h). Is there a way to solve this problem?

    Reply
    1. Jonathan Drummey Post author

      Hi,

      Tableau only has two ways to lay out axes – using time or numbers (integers or decimals). This dd:hh:mm:ss formatting is using a (fake) time format for number value and therefore the axis is being laid out in base 10. What I’ll typically do is a date or datetime field as the axis and turn off Show Header and Include in Tooltip, and instead use reference lines and/or mark labeling to provide context.

      Reply

Please add your thoughts and perspectives