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 25, 2018 in Tableau by ghost
• 1,790 points
1,172 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 25, 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 12, 2018 in Tableau by xyz
• 1,560 points
7,022 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, 2019 in Tableau by Cherukuri
• 33,030 points
12,123 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 18, 2018 in Tableau by ghost
• 1,790 points
778 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
1,081 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
9,900 views
+2 votes
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
1,598 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 6, 2018 in Tableau by Atul
• 10,240 points
5,650 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 25, 2018 in Tableau by Atul
• 10,240 points
778 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP