14 Replies Latest reply on Aug 17, 2017 2:42 PM by Reza Rahimi

    Sum hours:minutes & result displays more than 24hrs

    Caron Lloyd

      My data in the background is 2 columns showing the START Date & Time (dd/mm/yy hh:mm) and the END Date & Time (dd/mm/yy hh:mm)

       

      I've created a calculated field to work out the hh:mm between start and finish for each

      [END]-[START]

      However Tableau won't total hours over 24hrs i.e. 25hrs 30mins is shown as 1hr 30mins.

       

      I'd like to sum the total hours over the month which I've worked out to be 97hrs 22mins but calculate this in Tableau it's showing me 1hr 22mins?

       

      How do I get it to add the total hours?

        • 1. Re: Sum hours:minutes & result displays more than 24hrs
          Roy Dar

          If I understand you correctly you want to get the time between the two dates (for example duration).

          You can use DATEDIFF function for that.

          • 2. Re: Sum hours:minutes & result displays more than 24hrs
            Caron Lloyd

            yes I want to get a total hh:mm duration between two date & times and an average

            - please can you explain how I do that using Datediff?

             

            Thanks

            • 3. Re: Sum hours:minutes & result displays more than 24hrs
              Caron Lloyd

              Hi Roy

               

              Are you able to help me? I've tried using the DATEDIFF function but am not able to get the result I want, which is the sum total hh:mm?

              Tableau won't give me the totals when they go beyond 24 hours, in excel you use the format [h]:mm to get the result.

               

              Please I'm desperate to resolve this.

              • 4. Re: Sum hours:minutes & result displays more than 24hrs
                Roy Dar

                Hi,

                 

                You can use the DATEDIFF to get the hours diff and run it again to get the minutes diff, hover you will need to subtract the hours from the minutes to get the result.

                It should look something like this:

                 

                hours_diff = DATEDIFF("hours",....)

                minutes_diff = DATEDIFF("minutes",....)

                 

                result =  hours_diff  + ":" + (minutes_diff - (hours_diff * 60 ))

                 

                I hope this will work for your requirements.

                 

                - Roy

                • 5. Re: Sum hours:minutes & result displays more than 24hrs
                  Caron Lloyd

                  Hi Roy

                   

                  This solution gives an error message: can't add integer and string values

                   

                  hours_diff  + ":" + (minutes_diff - (hours_diff * 60 )) The first plus sign is where the error occurs as the ":" is a string value.

                   

                  However I don't want a string value as I'm using these metrics in calculations which I'm pulling into graph's.

                   

                  I basically I'm looking at delays over a number of start and end date/times  - I'm calculating delay times and from these I want to add the individual delays to get  total hours in the hh:mm format.

                   

                  Does that make sense?

                  • 6. Re: Sum hours:minutes & result displays more than 24hrs
                    Shawn Wallwork

                    Caron, if you need to insert the ':' then you ultimately will end up with a string. So to get rid of the error you'll need to do this:

                     

                    STR(hours_diff)  + ":" + STR((minutes_diff - (hours_diff * 60 )))

                     

                    Any math you need to do will need to be done inside the STR() function. Hope this helps.

                     

                    --Shawn

                    • 7. Re: Sum hours:minutes & result displays more than 24hrs
                      Caron Lloyd

                      Hi Shawn

                       

                      Per my reply above, the answer as a string value won't help me as I'm trying to run metrics off the total hour/minute  values and pull them into graphs, do you have a solution that gives me total values as hh:mm?

                       

                      I've attached an example showing the pie chart that I'm trying to build, currently the delay shows 7hrs 16mins but it should read 31hrs 16 mins.

                       

                      The averages are also out of whack?

                       

                      Any help would be much appreciated.Example.jpg

                      • 8. Re: Sum hours:minutes & result displays more than 24hrs
                        Shawn Wallwork

                        Caron, try something like this:

                         

                        STR(DATEDIFF('hour',[Order Date],[Ship Date])) + ':' +

                        STR(DATEDIFF('minute',[Order Date],[Ship Date])/60-

                        INT(DATEDIFF('minute',[Order Date],[Ship Date])/60))

                         

                        The last two lines are just a way to figure out the remainder of of the calculation, so you don't end up doubling the hours. It's the left over minutes. You're probably going to have some formatting issues with the way minutes are displayed. If you don't like the way single digits are displayed we can work on that next. But first let's make sure this is what you're looking for.

                         

                        --Shawn

                        • 9. Re: Sum hours:minutes & result displays more than 24hrs
                          Caron Lloyd

                          Hi Shawn

                           

                          I've attached a demo workbook for you, which may give you an idea of what I'm trying to acheive.

                          If I use your programming above it returns string values which can't be pulled into the graphs and tables I'm trying to create.

                          Dashboard "Train arrival & Load Charts", Train 2 should be showing a total SILO breakdown delay of 31hrs 16 min i.e. The total hours not time.

                           

                          Then in the pie chart below it needs to be the average delays per train - again this metric is not correct due to some of the delays being over 24hrs and Tableau not showing this.

                           

                          Do that make sense?

                          • 10. Re: Sum hours:minutes & result displays more than 24hrs
                            Richard Leeke

                            The basic issue is that Tableau doesn't support displaying a duration in hh:mm format.

                             

                            When you calculate the total SILO breakdown delay for train 2, under the covers Tableau has a number representing the total elapsed time (actually 55 hours 16 minutes, not 31 hours 16 minutes).

                             

                            It will help to understand the way that Tableau (and in fact Excel) represent dates and times. Under the covers, a date and time is held as a fractional number representing the number of days since an arbitrary date regarded as day zero (which just happens to be 30 December 1899 - don't ask!). So for example, it is currently 7pm on 20 November 2012 for me. Under the covers that is represented as 41233.79.

                             

                            So when you work out the difference between two datetime values, what you get is a number representing the number of (fractional) days between them. In the case of the total train 2 SILO delays, that value is 2.3028 (which you can see by changing the field format to a number with a few decimal places). That represents 2.3028 days. .3028 of a day is 7 hours 16 minutes.

                             

                            What you have done by setting the number format to hh:mm is tell Tableau to format that number as a datetime - but only display the hours and minutes. So Tableau thinks the number represents 2 days, 7 hours and 16 minutes since the beginning of time. If you change the format mask to dd/mm/yyyy hh:mm it will display as 01/01/1900 07:16 - because 1st January 1900 is 2 days after 30 December 1899 when Microsoft thinks the world began. Most of your other numbers will show as being on 30/12/1899, because they represent less than 24 hours.

                             

                            So that's why you are seeing what you are seeing. As for what to do about it, that's a bit trickier.

                             

                            The easiest thing, though maybe not acceptable, would be just to display the number. Displaying it as fractional days is pretty ugly, but if you multiply all the durations by 24, you will at least get it as fractional hours. So the 55 hours 16 minutes would show as 55.27 (.27 of an hour is 16 minutes). Or you could multiply by 24 x 60 to get the number in minutes: 3,316 minutes.

                             

                            The only other suggestion is to convert to strings, as Shawn and others have said. To make that work with your pie charts you will need to restructure your data quite a bit, though - and even then I'm not sure that will work.

                             

                            Let me know whether fractional hours are an option - if not I'll have a better look to see if there is an option to restructure.

                            • 11. Re: Sum hours:minutes & result displays more than 24hrs
                              Caron Lloyd

                              Hi Richard

                               

                              per your email I've done as you suggested and converted the time duration's to minutes, then string values to get the hh:mm format

                               

                              (I'll need some pointers on the programming in the background as I'm getting some results showing 60 mins?)

                               

                              Ultimately I'm sure we can make the math work using your solution but it doesn't translate well into graphs.

                              1. On the line graph - every single string value gets listed along the axis - I don't see any way to change this other than having no axis headers which I don't want.

                              2. You can't run trend lines over these results

                              3. If I have early arrivals I can't overlay the results on 2 line graphs using dual axis?

                               

                              Do I admit defeat and leave this analysis in excel?

                              • 12. Re: Sum hours:minutes & result displays more than 24hrs
                                Richard Leeke

                                Yeah, it gets very messy very quickly, doesn't it?

                                 

                                I had a quick play in the attached version - a couple of ideas but not really getting to where you were hoping.

                                 

                                I'm puzzled by the fact that your formula displayed 180 minutes as 03:60. 180 mod 60 should be zero not 60 - I don't know what's going on there - possibly some rounding error. I've bodged it for you by adding 1 before doing the modulo and then subtracting 1 afterwards. I don't think that will cause errors in any other cases - though if it is a rounding issue it might do something like make 59 minutes come out as -1 sometimes - so it's not really a very good solution.

                                 

                                The format of the early arrival (0:-35) still needs some work - but a quick attempt at doing that ran into issues with the expression being too complex for the MS Jet engine (used for accessing text files and spreadsheets) to cope with. You'd have more chance with a data extract.

                                 

                                I had a go at making the axis labelling work better. To do that I made the axis continuous, using the delay calculated just as the difference between scheduled and actual arrival, and then just formatted it as hh:mm, with tick marks every 0.125 days (every 3 hours). So that will go 00:00, 03:00, 06:00 ... 21:00, 00:00, 03:00. i.e, no days on the axis scale. If you label the individual points with your string version of the delay, that makes it just about intelligible.

                                 

                                Still not really getting to where you want to. There are some ideas in the ideas section about more control over formatting, maybe you should go and vote for them,

                                • 14. Re: Sum hours:minutes & result displays more than 24hrs
                                  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)