4 Replies Latest reply on Mar 19, 2012 2:01 PM by Mark Holtz

    Month, Year format

    Emily Mueller

      I have an excel workbook as a source with Month as a field and Year as a field.  They show up in Tableau as string and # formats, and I can't figure out how to change them to date format.  I tried creating a heirarchy, but that didn't seem to work either.  Does anyone have a suggestion on either how to format my source data, or format the information once in Tableau so that I can report on these as Date formats?

       

      Any help is greatly appreciated!

        • 1. Re: Month, Year format
          Tracy Rodgers

          Hi Emily,

           

          A calculated field will need to be created for these fields to be recognized as a date in Tableau. The calculation should look similar to the following:

           

          date(str([Month field]) + '/' + str([Day field]) + '/' + str([Year]))

           

          If you have no day field, substitute a 1 so that the calculation would look like the following:

           

          date(str([Month field]) + '/1/' + str([Year]))

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Month, Year format
            Emily Mueller

            Thanks Tracy! That worked!

            • 3. Re: Month, Year format
              shilpa g

              how can i extract only hrs in datetime field?

               

              i need to compare the hrs and days

               

              like

              <4hrs - 1 day

              1 day - 2 days

              2 days - 4 days

              > 4 days

               

              Please help.

              • 4. Re: Month, Year format
                Mark Holtz

                I think you could create a calculated field to get the Hours Difference.

                 

                Hours Difference:

                datediff('hour',[Start Date],[End Date])

                 

                Then assign a bin based on the calculated field result.  I believe Tableau offers several ways to create bins, but I'll show a calculated field:

                 

                Hours Bin (using if statement)

                if [Hours Difference] < 4 then 'Less than 4 hours'

                elseif [Hours Difference] < 24 then '4 hours to 1 day'

                elseif [Hours Difference] < 48 then '1 day to 2 days'

                elseif [Hours Difference] < 96 then '2 days to 4 days'

                else 'More than 4 days'

                end

                 

                You could also just put it all in a single calculated field:

                if datediff('hour',[Start Date],[End Date]) < 4 then 'Less than 4 hours'

                elseif datediff('hour',[Start Date],[End Date]) < 24 then '4 hours to 1 day'

                elseif datediff('hour',[Start Date],[End Date]) < 48 then '1 day to 2 days'

                elseif datediff('hour',[Start Date],[End Date]) < 96 then '2 days to 4 days'

                else 'More than 4 days'

                end