4 Replies Latest reply on Nov 8, 2018 9:51 AM by Paul Wachtler

# Average Time Difference in HH:MM:SS from given two dates

Hi,

I have a data set that has received and processed times by cust_id.

Aim:To get average time take to process by cust_id in -hh:mm:ss.

Here are the calculations I did so far..

Time difference in seconds:        ([Processed time]-[Sent time])*86400

avg.Time difference in seconds:avg([Time difference in seconds])

hh:mm:ss calculation:                 IIF([avg.Time difference in seconds] % 60 == 60,0,[avg.Time difference in seconds] % 60)// seconds

+ IIF(INT([avg.Time difference in seconds]/60) %60 == 60, 0, INT([avg.Time difference in seconds]/60) %60) * 100 //minutes

+ INT([avg.Time difference in seconds]/3600) * 10000 //hours

After changing the format to HH:MM;SS ,I don't see any numbers.Any thoughts on resolving this issue will of great help.Attached are the workbook and sample data.

Thanks,

Siri

• ###### 1. Re: Average Time Difference in HH:MM:SS from given two dates

Hi Sirisha,

Your hh:mm:ss calculation field is more complicated than it has to be. Tableau can handle formatting directly from seconds to hh:mm:ss for you so you don't have to calculate each part separately.

Simply change that calculation to this and it will give you what you're looking for:

[avg.Time difference in seconds] / 86400

Best,

Paul

• ###### 2. Re: Average Time Difference in HH:MM:SS from given two dates

Divide seconds by 86400 and format the result to hh:mm:ss

• ###### 3. Re: Average Time Difference in HH:MM:SS from given two dates

Thanks Deepak and Paul,

Now, I have the numbers populating but those are right only for <=24hr window >24hr(86400) I have all wrong. Any thoughts on this.

Thanks,

Siri

• ###### 4. Re: Average Time Difference in HH:MM:SS from given two dates

Ah that's my fault, that only works under 24 hours.  For data with values over 24 hours, you were on the right track with your initial calculation but you need to use some str() functions to get it to work.  This will give you what you're looking for:

STR(INT(([avg.Time difference in seconds])/3600))+ ":" +

IF INT(([avg.Time difference in seconds])%3600/60)

< 10 THEN "0" ELSE "" END + STR(INT(([avg.Time difference in seconds])%3600/60))+ ":" +

IF INT(([avg.Time difference in seconds]) %3600 %60)

< 10 THEN "0" ELSE "" END + STR(INT(([avg.Time difference in seconds])%3600 %60))

Shoutout to Mahfooj Khan for working out that calc in this thread: