8 Replies Latest reply on Sep 19, 2012 9:00 AM by Sriram Srinivasan

    12 hour clock format

    Sriram Srinivasan

      I have the data in the form of 05/01/2011 2:30:00 AM

       

      I need this data in the form of 05/01/11 2 AM

       

      Basically, I want to extract hourly data and the format should be strictly 12 hour clock format.

       

      Please help

        • 1. Re: 12 hour clock format
          Shawn Wallwork

          Sriram,

           

          Rt-click the date field, go to Field Properties\Date Format then scroll down to 'Custom'. Then paste this into the box:

           

          m/d/yy h AMPM

          or

          mm/dd/yy h AMPM   if you want the leading zeros.

           

          --Shawn

          • 2. Re: 12 hour clock format
            Sriram Srinivasan

            Thanks for the reply.

             

            Can you please help me in doing the same using a calculated field please?

            I am using old version of tableau and hence not able to follow the steps you mentioned.

            • 3. Re: 12 hour clock format
              Tracy Rodgers

              Hi Sriram,

               

              Which of version of Tableau Desktop are you using? The steps described above are the same for 6.0.

               

              -Tracy

              • 4. Re: 12 hour clock format
                Sriram Srinivasan

                My Bad. It works fine in my version too but I have an issue.

                 

                when I use this calculation, I get the hour correctly like 1:00, 2:00, 3:00 etc

                str(DATEPART('hour',[End Time]))

                +

                ":"

                +

                "00"

                But when I use the above steps of formatting as replied by shawn then, I get data like 1 AM, 1AM, 1 AM etc for every variation in minute.

                 

                For a given day, if there are several values for minutes, I need to extract only the hour and only once like 1 AM, 2 AM, 3 AM etc. Can you help me in achieving this?

                • 5. Re: 12 hour clock format
                  Shawn Wallwork

                  I'm confused. If you can't share your workbook, then could you post some screen shot to help us figure out what you're trying to accomplish. And if your calculated field works correctly, what is it that doesn't work? Why aren't you using it?

                   

                  --Shawn

                  • 6. Re: 12 hour clock format
                    Sriram Srinivasan

                    I am pasting the screens. This one was obtained using the formatting method. There are more entries in axis for 3AM, 5AM and others but I need only one. Like 5AM, 6AM, 7AM etc

                    Untitled.png

                    This one is obtained using formula

                    Untitled.png

                     

                    I need to display as seen in my first screen but the interval should be hourly as seen in second screen

                    • 7. Re: 12 hour clock format
                      Shawn Wallwork

                      OK Sriram, got it. Try these steps:

                       

                      1. Change your date pill from discrete (blue) to continuous (green). This will change your labels to a horizontal orientation, but more importantly it will give you access to the 'Edit Axis...' option.
                      2. Rt-click the axis, click Edit Axis...
                      3. Click the 'Tick Marks' tab, and change the setting to this:

                      Hours.png

                      4. Rt-click the axis again and click 'Format'. Then click the triangle next to date, and enter the date format into the custom field:

                       

                      Hours2.png

                       

                      This should produce something like this:

                       

                      Hours3.png

                       

                      When you tell Tableau you want continuous dates, then you can set up intervals on the hour. Also Tableau does some label management so you don't get overlapping text. When you use discrete dates, you're telling Tableau to show every date, period. So Tableau rotates your label and shows the whole date for each mark. That's why you're getting the doubled up hours. If you showed the minutes then the date would make sense.

                       

                      Anyway these are your choices, continuous or discrete, horizontal or vertical, and single hours or multi-hours.

                       

                      --Shawn

                      • 8. Re: 12 hour clock format
                        Sriram Srinivasan

                        Thanks, that works...