How can I find the time difference between two columns

+1 vote

Sample Data:

+-------------------------+-------------------------+
| StartTime               | FinishTime              |
+-------------------------+-------------------------+
| 2015.09.06 12:56:03 GMT | 2015.09.06 12:56:30 GMT |
| ...                     | ...                     |
+-------------------------+-------------------------+

I want another column to calculate duration.

I have already used DataDiff Function in Tableau, but somehow I am getting an error.

Jul 24, 2018 in Tableau by ghost
• 1,790 points
41 views

1 answer to this question.

+1 vote

Based on the data you showed, I'm guessing you want the difference in seconds between the times. You're right to use DATEDIFF(). (I noticed you said DATADIFF, so careful that you're using the right method.)

Use the following calculated field:

DATEDIFF('second', [StartTime], [FinishTime])

Adjust that first argument accordingly if I guessed wrong on the unit of time you want.

If Tableau's having trouble automatically parsing StartTime and EndTime as datetimes, you can use DATEPARSE():

DATEPARSE("yyyy.MM.dd hh:mm:ss", [StartTime])

As for displaying the duration in hours, minutes, and seconds, DATEDIFF() just returns a single number, so you'll have to homebrew something. You'll need to create your own function so you can format the duration according to your needs, but here's one quick example that displays the duration as "hh:mm:ss" (you'll need to make a calculated field like I showed above to find the duration in seconds first):

RIGHT('0' + STR(FLOOR([Duration in Seconds]/60/60)), 2) + ':' + 
RIGHT('0' + STR(FLOOR([Duration in Seconds]/60)), 2) + ':' + 
RIGHT('0' + STR(FLOOR([Duration in Seconds])), 2)

On each line, you divide the duration in seconds by the appropriate divisor to convert to hours, minutes, or seconds. Then you FLOOR that number to trash the decimal. To deal with single digit numbers, you concatenate a '0' to the front of each number, then take the right two characters of the resulting string. (RIGHT('027', 2) yields '27', and RIGHT('04', 2) yields '04'.)

answered Jul 24, 2018 by Atul
• 10,240 points
Thanks, i had the same problem. it works well. Good and clear explanation.

Related Questions In Tableau

0 votes
1 answer

How can I calculate the median of sales price using 3 variables in Tableau

First let me clarify things for you. ...READ MORE

answered Apr 11, 2018 in Tableau by xyz
• 1,550 points
736 views
0 votes
2 answers

How can I add text in the centre of a Donut Chart ?

Hi, You can read this for creating a donut ...READ MORE

answered Apr 4 in Tableau by Cherukuri
• 28,040 points
747 views
0 votes
0 answers

How can I add a connection between Mongodb and Tableau

What I want? Parse the XML to JSON ...READ MORE

May 17, 2018 in Tableau by ghost
• 1,790 points
150 views
0 votes
2 answers

How can I display filtered value and grand total in the same sheet?

You can create a fixed calculated field ...READ MORE

answered Sep 15, 2018 in Tableau by Machdata
• 340 points
85 views
0 votes
1 answer

How to color code cells of a column based on the text value in Tableau

You can use the following steps to ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,240 points
2,037 views
+1 vote
1 answer
0 votes
1 answer

Calculated filed with if-then

Below is the required code:  IF (NOT ISNULL([test2])) ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,240 points
89 views
+2 votes
1 answer
0 votes
1 answer

How can I count the aggregate function in Tableau

Create two calculated fields one for 100's ...READ MORE

answered Apr 5, 2018 in Tableau by Atul
• 10,240 points
1,735 views
+1 vote
1 answer

How can I calculate the % of completion

SUM(IIF([Status]="Delivered",[Point],NULL)) / SUM([Point]) I assume that your column ...READ MORE

answered Jul 24, 2018 in Tableau by Atul
• 10,240 points
34 views