9 Replies Latest reply on Sep 16, 2015 4:25 PM by Emily Chen

# Find Duration between two columns

I have two columns of data of the following format:

 FinishTime SubmitTime 2015.09.06 12:31:20 GMT 2015.09.06 12:31:04 GMT

I want a third column finding the time difference (ie to find the duration)

Sometimes the data will be like this:

 2015.09.06 00:15:23 GMT 2015.09.05 23:58:05 GMT

Here the time difference is a bit complicated where date must also be considered.

I am new to Tableau. Help would be appreciated. Thanks.

• ###### 1. Re: Find Duration between two columns

Hi Dinesh,

I think for anyone to answer this question, could you give an indication of what format you want/need your time difference to be in? Should it be hours, minutes (especially if it can span days)? What should it look like?

Dana

• ###### 2. Re: Find Duration between two columns

Hi Dinesh,

Here is an excellent blog post (with downloadable workbook) describing how to calculate the time difference in seconds: Data Viz: Tableau Tip : How to Calculate Time Difference The formula can be altered to calculate this at a less granular level

• ###### 3. Re: Find Duration between two columns

Hi Dinesh,

Calculating the difference between two dates is very easy in Tableau. First of all, make sure that your two dimensions are, in your case, in a "Date & Time" format. you can check it double clicking in each one > Change Data Type and then select Date & Time if it's not selected.

Then create a new calculated field (Analysis menu > Create Calculated Field) name it as you want and then use the DATEDIFF function, For example:

DATEDIFF('hour', [start date], [end date], 'monday')

This will create a new field that calculates the difference in hours between the start date and the end date, taking Monday as the start of the week. In your case if you want the time in minutes for example it will be:

DATEDIFF("minute',[SubmitTime], [FinishTime}, 'monday')

Or 'sunday' at the end if you consider that your week starts on Sunday, not Monday.

• ###### 4. Re: Find Duration between two columns

It should be in minutes and seconds....

• ###### 5. Re: Find Duration between two columns

when i change the datatype to datetime, i lose the data fully in that column and only null values appear.

• ###### 6. Re: Find Duration between two columns

Hi Dinesh,

could you upload a packaged workbook to have a look at it?

• ###### 7. Re: Find Duration between two columns

i have uploaded a sample excel file similar to my workbook for now....

• ###### 8. Re: Find Duration between two columns

You can use DATEPARSE function to convert your Date string dimension to a Date dimension.

And then use the DATEDIFF function to calculate the duration between them.

• ###### 9. Re: Find Duration between two columns

To add to Pablo Saenz de Tejada's reply, his reco works really well if your data is always in 1 time zone. If your timezones change, you'll have to convert it into ISO time as date parse doesn't account for time zone differences.