# TABLEAU: Display days, hours, minutes, and seconds.

###### TABLEAU: Display days, hours, minutes, and seconds.
Category:
Question ID: 109854
 ▲ 0 ▼ ♥ 0 Hi, I need to display the number of days, minutes, hours, seconds a product is on hold.  How can I show date difference in written duration using Tableau? Ex. 0 days 7 hours 48 minutes 10 seconds Thanks Marked as spam Posted by followthedata (Questions: 13, Answers: 4) Asked on October 7, 2020 10:28 am 697 views
 ▲ 0 ▼ ✔ Private answer Yes, you can display # days, # hours, # minutes, and # seconds.  You will need to create several calculations. Step 1: Hold Time Diff: this is a level of detail (your group by in SQL) {FIXED [id], [category], [dates]: SUM(DATEDIFF(‘second’,[Start Date], [End Date])} Step 2 -6: Create Hold Days, Hold Hours, Hold Minutes, Hold Seconds, which we will use to display hold days, but more importantly, show hours/mins/sec when the hold is on the same day, which will show "0" under 'Hold Time Days' (see calc below) Hold Days: (SUM([Hold Time Diff]) - (SUM([Hold Time Diff])%(24*3600)))/(24*3600) Hold Hours: (((SUM([Hold Time Diff])%(24*3600))) - (SUM([Hold Time Diff])%(24*3600))%3600)/3600 Hold Minutes (((SUM([Hold Time Diff])%(24*3600))%3600) - ((SUM([Hold Time Diff])%(24*3600))%3600)%60)/60 Hold Seconds IF INT(SUM([Hold Time Diff]) %3600 %60) < 10 THEN "0" ELSE "" END + STR(INT(SUM([Hold Time Diff]) %3600 %60)) Step 6 – Combine the above Holds for Days, Hours, Minutes, and Seconds to create a readable Hold Duration field. Hold Duration: IF [Hold Days] > 0 THEN STR([Hold Days])+" Day"+" "+ STR([Hold Hours])+" Hr"+" "+ STR([Hold Minutes])+" Min"+" "+ STR([Hold Seconds])+" Sec" ELSE STR([Hold Hours])+" Hr"+" "+ STR([Hold Minutes])+" Min"+" "+ STR([Hold Seconds])+" Sec" END Happy Tableau-ing! Marked as spam Posted by Angela Cooper (Questions: 4, Answers: 14) Answered on October 7, 2020 10:33 am