8 Replies Latest reply on Dec 11, 2013 9:05 AM by keren.abina-sotomayor

    Convert string hours to numbers

    keren.abina-sotomayor

      Hello,

       

      I have several fields that represent total number of hours, and these fields are strings. I need to change them to numeric. The field format is "02:00" representing 2 hours, "02:20", representing 2 hours and 20 minutes. Clicking on the change type menu doesn't work.

       

      In case this information is needed, I will later be calculating percentages using these fields.

       

       

       

      Thank you!

        • 1. Re: Convert string hours to numbers
          Steve Martin

          Hi Keren,

           

          Are you tring to convert these fields to float or do you want them to display as hh:mm:ss?

           

          If its the float (I suspect so so you can do percent calcs), the calculation you need is:

           

          (

          DatePart('hour',<YourDate>))*3600 +

          (DatePart('minute',<YourDate>))*60 +

          DatePart('second',<YourDate>)

          )/3600

           

           

          --

          In this instance, 02:20 as a decimal works out to 2.33

          • 2. Re: Convert string hours to numbers
            keren.abina-sotomayor

            Hi Steve,

             

            I need to be able to calculate percentages with them.

             

            For example, one column represents hours scheduled to be in class  as "04:00" and the other column represents actual hours in class as "02:30". I need to be able to calculate a percentage with those values to get an attendance rate.

             

            If hh:mm:ss format allows this, then I would like that format so as to get the most accurate information. Otherwise, I would need a regular numeric format.

             

            Thanks!

            • 3. Re: Convert string hours to numbers
              Paramesh Sangangiri

              Hi Keren,

               

              I'm attaching a link of Tableau video which might help you in converting integers to time format and then helping you in resolving your issue. Kindly have a look at it and let us know for further proceedings.

               

              Date Calculations | Tableau Software

               

              Regards,

              Paramesh

              • 4. Re: Convert string hours to numbers
                Steve Martin

                Hi Keren, just updated my last message (see above)

                • 5. Re: Convert string hours to numbers
                  keren.abina-sotomayor

                  Hello,

                   

                  Thank you all for your help.

                   

                  The fields I'm referring to are not date fields. They are string fields that represent number of hours. Below is a sample. The column names with the word 'hours' on them show up as string dimensions on Tableau. Those are the fields I want to convert to numeric measures. I hope this is helpful. thanks!

                   

                  dateEngaged Hours - AppointmentsEngaged Hours - ClassEngaged Hours - TotalSchedule Hours - AppointmentsSchedule Hours - ClassSchedule Hours - Total
                  6/3/2013 0:000:000:000:000:001:301:30
                  6/3/2013 0:000:000:000:000:001:301:30
                  6/3/2013 0:000:000:000:000:001:301:30
                  6/3/2013 0:000:000:000:000:001:301:30
                  6/3/2013 0:000:000:000:000:001:301:30
                  6/3/2013 0:000:000:000:000:001:301:30
                  6/3/2013 0:000:000:000:000:001:301:30
                  6/3/2013 0:000:000:000:000:001:301:30
                  • 6. Re: Convert string hours to numbers
                    Steve Martin

                    I'm able to copy that piece of text straight into Tableau and set the data type to date and time (which is what is needed to perform the above calcs).

                     

                    Please can you attach a copy of your source data pref in Excel so that I can plug it into Tableau at my end.

                     

                    Also, whilst this isn't normally an issue for Tableau, if this is coming from Excel with the default set to Number formatted as text then Jet may well be providing this to Tableau in a way that is preventing Tableau from reading it correctly so changing this in Excel may be of use - Excel shall auto-format a time field to custom (dd/mm/yyyy hh:mm:ss) but in reality it shall be an integer.

                     

                    Finally, within Tableau, you may be able to create a calculated field to create a date-time field using DateTime(<YourField>)

                    1 of 1 people found this helpful
                    • 7. Re: Convert string hours to numbers
                      Paramesh Sangangiri

                      Hi Keren,

                       

                      It appears to me that the time format present in your text file is not read properly by Tableau because of no date being assigned with it, however if your data is of the format hh:mm:ss we can convert them to numeric format as following:

                       

                      Datetime(sum(float([Col-Name])))

                       

                      The above calculation makes Tableau understand your text in a suitable time format, then you can use various formulas to extract hours, minutes or even seconds as integer and make your calculations.

                       

                      datepart('hour',[Col-Name]) to extract Hours part and you can drill down all the way till seconds.

                       

                      Hope this helps you to some extent and if I misunderstood your requirement, kindly share the packaged workbook to assist you further.

                       

                      Regards,

                      Paramesh

                      • 8. Re: Convert string hours to numbers
                        keren.abina-sotomayor

                        Sorry I'm coming back to this so late, but Thank you Steve and Paramesh!

                         

                        The key comments that helped me understand what was going on were Steve's "set the data type to date and time (which is what is needed to perform the above calcs)"  and Paramesh's "It appears to me that the time format present in your text file is not read properly by Tableau because of no date being assigned with it, however if your data is of the format hh:mm:ss we can convert them to numeric format".

                         

                        I decided that what I wanted was the total of minutes in each column and the calculation I ended up using was:

                         

                        DatePart('hour',[AppointmentScheduledHours])*60 +

                        DatePart('minute',[AppointmentScheduledHours])