5 Replies Latest reply on Jun 5, 2012 8:18 AM by Tracy Rodgers

    Formatting Date Literals and using DATEDIFF()

    Paul Hamrick

      Hello All,

       

      Brand new user here, and a non-programer to boot!  I'm hoping that someone can help get me started

       

      I'm using Excel spreadsheets and I have some date ranges that I need to:

       

      Determine the interval between two pairs of dates:  [date2]-[date1]   and   [date4]-[date3]

      Add the resulting ranges to get the sum in Year, Month, Day:    [date2]-[date1]+[date4]-[date3]= Years, Months, Days

       

      The first question I have is formatting date literals, can I do this in Tableau?  Do I just add the "#" to the date fields in the calculation window?

      DATEDIFF(date_part, #[date1]#, #[date2]#) for example?   Do I need to bring the data into Tableau from Excel formatted as #mm/dd/yyyy# or can I import it in the mm/dd/yyyy format and add the literal designation in Tableau?  How?

       

      Next question is if using DATEDIFF is even appropriate here as I need the date_part to return year month and day?  Is there a syntax for DATEDIFF that will return the interval in year, month, and day?

       

      I'm obviously floundering here , any help or links to resources/examples would be greatly appreciated.

       

      Thanks!

        • 1. Re: Formatting Date Literals and using DATEDIFF()
          Tracy Rodgers

          Hi Paul,

           

          Are the dates that you want to find the difference intervals between in your data? If so, they should be in mm/dd/yyyy format in Excel, so that they are recognized as dates within Tableau. You will be able to tell if Tableau recognizes them as dates, as they will have a calendar symbol next to the them in the data box. However, if the calculated fields are being written and a date that is not in the data (or a specific date) wants to be included in the field, then the date should be enclosed with pound (#) signs.

           

          For your last question, is the outcome supposed to be a date? If so, a calculation similar to the following could be used:

           

          date(str(datediff('month', #July 1, 2012#, #August 1, 2012#)) + '/' + str(datediff('day', #July 1, 2012#, #August 1, 2012#) )+ '/' +str(datepart('year', #July 1, 2012#)))

           

          Or do you just want the number of days, months and years? If this is the case, something like this could be used:

           

          str(datediff('month', #July 1, 2012#, #August 1, 2012#)) + ', ' + str(datediff('day', #July 1, 2012#, #August 1, 2012#) )+ ', ' +str(datediff('year', #July 1, 2012#, #August 1, 2012#))

           

          Hope this helps a bit!

           

          -Tracy

          • 2. Re: Formatting Date Literals and using DATEDIFF()
            Paul Hamrick

            Hi Tracey,

             

            Awesome!  Thanks for your help!

             

            I do have all of the dates in the Excel files and they are formatted as mm/dd/yyyy format.  I have about 8,000 records and each record has 4 dates so adding the "#" manually is not going to work.  If Tableau imports and recognizes the data fields as dates, do I even need to add the # to make it a literal? 

             

            If I do need to append each date with #, is there an easy way to do it in Tableau?  How?

             

            As for the calculation, I need the result to be in the Year, Month, Day format so your second example is perfect (I'm guessing it won't change the calculation to rearrange the DATEDIFF in Year, Month, Day order).

             

            I'm not a coder (not even close!) so the syntax is a little confusing to me, is there a resource that you suggest that would help me become more comfortable with how calculations are structured?  I really don't know what the difference is between and string and an expression or why I'd use one over the other and I hate to ask accomplished users to spoon feed me.

             

            Thanks again for your examples, this community is so helpful!

            • 3. Re: Formatting Date Literals and using DATEDIFF()
              Tracy Rodgers

              Hi Paul,

               

              No need to add the # signs if your dates are in your data, as mentioned, it would only need to be done if you were trying to choose a very specific date.

               

              You are right about the order, you can easily rearrange the calculation to get it in the desired order.

               

              In terms of learning calculations, you are in luck as there is a free live online training on Wednesday (June 6) for calculations:

               

              http://www.tableausoftware.com/learn/training

               

              Other good resources are the product manuals, the forums, and trial and error!

               

              -Tracy

              • 4. Re: Formatting Date Literals and using DATEDIFF()
                Paul Hamrick

                Hi Tracey,

                 

                Thanks for the suggestions, I'll check out the class on Wed.

                 

                Trial and Error for me is resulting only in Error, it's very frustrating.  The calculation that you wrote for me isn't working and there's no way I'd ever be able to come up with anything close. 

                 

                In the Excel spreadsheet, Start Date and End Date are date values in mm/dd/yyyy format

                 

                str(datediff('year', [Start Date], [End Date])) + ', ' + str(datediff('month', [Start Date], [End Date]) )+ ', ' +str(datediff('day', [Start Date], [End Date]))

                 

                The results are displaying in this format:  3, 37, 1131  which seems to be a datepart although the calculated values are wrong. 

                 

                Thanks again although I'm not sure where to go from here.

                • 5. Re: Formatting Date Literals and using DATEDIFF()
                  Tracy Rodgers

                  Hi Paul,

                   

                  Would you be able to post your workbook (make sure to save it as a twbx file)?

                   

                  -Tracy