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

Convert Integer to Minutes and add to Date Field

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

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

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

Conclusion:

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

The formula for this is:

FIELD:  [PICKUP TIME OLD]

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(

or

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

DATETIME(

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

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

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

**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:

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