4 Replies Latest reply on Nov 24, 2016 11:44 PM by ABRAHAM PETER

# How to convert a Date difference field in to Total Seconds?

Hi ,

I have a data set which has a field available in (h:mm:ss) format. Need to analyse the field in terms of Total Seconds.

Attaching here a sample workbook with fields- Late Time in (h:mm:ss) format and Team Name
Need to find out the Total Late time in Seconds for Different teams.

When i tried, Not getting the results in Total Late time (Seconds).

Rather than modifying the raw data for field- Late time in to seconds, How to sort it out in Tableau?

Abraham Peter

• ###### 1. Re: How to convert a Date difference field in to Total Seconds?

Hello Abraham,

Use string parsing functions like left(),mid(),right to extract hour minute seconds. Convert them to integer using (). Then add them up.

Hours3600+minute60+second

Create :

Hours:

datepart('hour',[Late Time (h:mm:ss)])

Minute:

datepart('minute',[Late Time (h:mm:ss)])

Seconds:

datepart('second',[Late Time (h:mm:ss)])

Total:

sum([hours]*3600)+sum([minute]*60)+sum([seconds])

Regards,

Sudhakar reddy

2 of 2 people found this helpful
• ###### 2. Re: How to convert a Date difference field in to Total Seconds?

Hi Abraham,

Please use this calculation,

INT(MID(STR([Date]),(

FINDNTH(STR([Date]),":",1))-2,2))*3600

+

INT(MID(STR([Date]),(

FINDNTH(STR([Date]),":",2))-2,2))*60

+

INT(MID(STR([Date]),(

FINDNTH(STR([Date]),":",2))+1,2))*1

-------------------------------------------------------

Sridhar Mani

1 of 1 people found this helpful
• ###### 3. Re: How to convert a Date difference field in to Total Seconds?

Try this! First change the default properties> Date Format of your Late Time field to dd:nn:ss

So that it only show you the time then using DATEPART() function you can get hour, minutes, second

DATEPART('hour',[Late Time (h:mm:ss)]) * 3600 +

DATEPART('minute',[Late Time (h:mm:ss)])*60 +

DATEPART('second',[Late Time (h:mm:ss)])

workbook attached for your reference.

Mahfooj

1 of 1 people found this helpful
• ###### 4. Re: How to convert a Date difference field in to Total Seconds?

Thanks Every one... Appreciate your help... :-)

Solutions from Sudhakar & Mahfooj are similar and i understand that DATEPART () and Summation is the key to this problem.

regards

Abraham Peter