6 Replies Latest reply on Jun 3, 2016 6:03 AM by Christopher McKinnish

    Convert Integer to Minutes and add to Date Field

    Christopher McKinnish

      Given a date field, and an integer that represents time with no decimals.   Example:  10 minutes 52 seconds is represented as 1052

       

      So what I need is 5/26/2016 10:52am

       

      The formulas I've used all produce and wrong result.  They're accepted by Tableau, just the wrong result.

       

      DATETIME([Dispatch Date]+ (((INT([Dispatch Time]*120)+ ([Dispatch Time]%100))/86400)))

       

      This formula is to concatenate the date field already present with the time so time duration can ultimately be calculated.

       

      I'm sure I've just been looking at this to long today, but could use some grey matter assistance with this one if anyone could offer some suggestions.

       

      Thanks!!!
      Chris

        • 1. Re: Convert Integer to Minutes and add to Date Field
          G Marc Turner

          Hi Christopher,

          Depending on the datasource, you might be able to use the DATEADD() function. I'm assuming that the part of the formula you have for converting the [Dispatch Time] to minutes is working correctly, so the function would look something like:

           

          DATEADD('minute', (((INT([Dispatch Time]*120) + ([Dispatch Time]%100))/86400)), [Dispatch Date])

           

          Hope this helps

          -Marc

          • 2. Re: Convert Integer to Minutes and add to Date Field
            Mahfooj Khan

            Hi,

            You can try something like this

            First custom the default number format of your Dispatch Time field like this

            Then create a combined fields using Dispatch Date and Dispatch Time separated by single space.

            Let me know If this help

             

            Mahfooj

            2 of 2 people found this helpful
            • 3. Re: Convert Integer to Minutes and add to Date Field
              Christopher McKinnish

              Conclusion:

               

              My Time is formatted as an integer in HHMM.  Therefore, 1802 is 6:02pm

              The formula for this is:

              FIELD:  [PICKUP TIME OLD]

               

              DATEADD('hour',int([Pickup Time OLD]/100),

              DATEADD('minute',int([Pickup Time OLD]%100)

               

              To add this to my date field is:

              FIELDS:

              1. TIME = PICKUP TIME OLD
              2. DATE = WD8
                1. Note:  WD8 is not a date but also an integer
                2. format = yyyymmdd.   This to has to be converted

               

               

              DATETIME(
              DATEADD('hour',int([Pickup Time OLD]/100),

              DATEADD('minute',int([Pickup Time OLD]%100),

              DATEADD('day',int([WD8]%100)-1,

              DATEADD('month',int(([WD8]%10000)/100)-1,

              DATEADD('year',int([WD8]/10000)-1000,#1000-01-01 00:00:00#))))))

               

              or

               

              if my date field was independently reformatted into a date, then I create a new calculated field with the following

               

               

              DATETIME(
              DATEADD('hour',int([Pickup Time OLD]/100),

              DATEADD('minute',int([Pickup Time OLD]%100),[INDEPENDENT DATE FIELD])))

               

              Why Tableau is so difficult with dates is beyond me, or why one thing works with EXCEL but not other databases (e.g., DATEPARSE, MAKEDATE)

              1 of 1 people found this helpful
              • 4. Re: Convert Integer to Minutes and add to Date Field
                Christopher McKinnish

                Thank you for taking the time, but ultimately I found the solution on my own, which I reposted to the original post.

                • 5. Re: Convert Integer to Minutes and add to Date Field
                  Christopher McKinnish

                  Thank you for taking the time, but ultimately I found the solution on my own, which I reposted to the original post.  However your post did help.  I had forgot that you could combine fields.   Had I not wanted to ultimately do a time duration calculation on the results, I would have used the combination method.  Thanks again.

                  • 6. Re: Convert Integer to Minutes and add to Date Field
                    Christopher McKinnish

                    **UPDATE  FOR THOSE USING IBM/AS400**

                    For those who may have their dates formatted as an integer AND the format is CYYMMDD (Century, Year, Month Day), and you need to convert this to a standard date format the formula is:

                     

                    DATE(dateadd('day',int(([Cyymmdd]+19000000)%100)-1,

                    (dateadd('month',int((([Cyymmdd]+19000000)%10000)/100)-1,

                    dateadd('year',int(([Cyymmdd]+19000000)/10000)-1000,#1000-01-01#)))))

                     

                    To Join this with a time field formatted as an integer:

                     

                    DATETIME(
                    DATEADD('hour',int([TIME]/100),  //gets the whole number for the hour

                    DATEADD('minute',int([TIME]%100), //uses the MOD function to get the minute portion of the time

                    DATEADD('day',int(([DATE]+19000000)%100)-1,

                    DATEADD('month',int((([DATE]+19000000)%10000)/100)-1,

                    DATEADD('year',int(([DATE]+19000000)/10000)-1000,#1000-01-01 00:00:00.000#))))))

                     

                    Note:  For IBM it is critical that you use the milliseconds in the date/time format.  Tableau handles the format either way, however, IBM will return an erroneous error from your ODBC and Tableau will not calculate!! 

                    1 of 1 people found this helpful