7 Replies Latest reply on Feb 23, 2016 10:20 AM by Patrick A Van Der Hyde

    Extract Hour from Date field

    Anita B

      Hi ,

      I have a  date field which give total duration spent by a person on particular task. The hour field has values like , " 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.

        • 1. Re: Extract Hour from Date field
          pooja.gandhi

          Can you attach some sample rows of your actual date field? Is the date field of a string data type? Which version of Tableau are you using? Which data source are you connecting to? A solution will largely depend on what answers you have to these questions.

          2 of 2 people found this helpful
          • 2. Re: Extract Hour from Date field
            Mark Fraser

            Hi Anita

             

            If you could provide the answers to Pooja's questions it would be helpful, as they tell us, how to help you...

             

            Assuming your field is a string, you could do something like this - (this may not work against all data sources)

            LEFT([Duration],FIND([Duration],':')-1)

            If the field isn't a string, you maybe able to simply convert using the STR() function, but it will depend on the format of the underlying data, it may not work.

             

            Cheers

            Mark

            • 3. Re: Extract Hour from Date field
              Anita B

              Hi ,

               

              I am using Excel as Source. The data type is Date. Here I have attached a sample sheet. I am using Tableau 8.1.

               

              Thanks,

              Anita.

              • 4. Re: Extract Hour from Date field
                Nicholas Hara

                Hi Anita,

                 

                Have you seen this blog post by Jonathan Drummey Formatting Time Durations in Tableau | Drawing with Numbers

                 

                I am pretty sure that it answers the question in far more detail than a typical forum reply.

                1 of 1 people found this helpful
                • 5. Re: Extract Hour from Date field
                  Jonathan Drummey

                  @Nicholas, thanks for the vote of confidence!

                   

                  It turns out that Anita had also asked the same question as a comment on that blog post, here's my reply:

                  Formatting Time Durations in Tableau | Drawing with Numbers

                   

                  Jonathan

                  2 of 2 people found this helpful
                  • 6. Re: Extract Hour from Date field
                    Anita B

                    Thank you All for taking time on my query and providing solutions.

                    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. Incase the value is more it might convert it to months too.

                     

                    Here is the calculated field : Total Hrs

                    IIF(DATEPART('day',[Total Time Spent (hrs)])=30,0,DATEPART('day',[Total Time Spent (hrs)]))*24+ DATEPART('hour',[Total Time Spent (hrs)]) + DATEPART('minute',[Total Time Spent (hrs)])/60 + DATEPART('second',[Total Time Spent (hrs)])/3600

                     

                    Here, I have attached screenshot of the sheet

                    for your reference.

                     

                    Here is the data from excel sheet :

                     

                    User
                      Name
                    Total Time
                      Spent (hrs)
                    D Ram405:00:00
                    Raju9:57:32
                    Anu9:01:46
                    Peter45:57:00
                    Chinu0:49:34
                    Minakshi39:00:45
                    Garshu0:07:09
                    John27:04:08
                    Hari117:46:10
                    Namit0:00:00
                    Krish0:00:11
                    Sonu

                    99:00:00

                     

                    Thanks,

                    Anita.

                    • 7. Re: Extract Hour from Date field
                      Patrick A Van Der Hyde

                      Anita - thank you for coming back and sharing the final calculation that worked in this instance.  I have marked your own answer as the "correct answer" so that others may know the answer is contained here.  Marking posts "Correct" also results in these posts appearing higher in search results for searches conducted within the Tableau Community Forums.

                       

                      Thank you again and come back again.

                       

                      Patrick

                      1 of 1 people found this helpful