KNOWLEDGE BASE

How To Aggregate Time


Published: 26 Feb 2020
Last Modified Date: 10 Jul 2020

Question

How to aggregate time

For example,

action A took time 1hr 20min 5sec, action B took time 1min 10sec

ACTION     TIME
action A      01:20:05
action B      00:01:10

to aggregate sum([time]), expected result is 1hr 21min 15sec (01:21:15)
 

Environment

  • Tableau Desktop

Answer

If time data & aggregate result are both less than 24 hrs

1. Create the following calculation

DATETIME(
SUM(
FLOAT(
DATEPARSE ( "HH:mm:ss", [Time] )
)
)
)

2. Set date format of the calculation as

custom: hh:nn:ss

If either time data or aggregate result is more than 24 hrs

1. Create the following calculation

INT(STR(INT(SUM(ZN(INT(SPLIT(STR([Time]),':',1)))*3600+ZN(INT(SPLIT(STR([Time]),':',2)))*60+ZN(INT(SPLIT(STR([Time]),':',3))))
/3600)))*10000
+
INT(RIGHT("0"+STR(INT((SUM(ZN(INT(SPLIT(STR([Time]),':',1)))*3600+ZN(INT(SPLIT(STR([Time]),':',2)))*60+ZN(INT(SPLIT(STR([Time]),':',3))))
%3600)/60)),2))*100
+
INT(RIGHT("0"+STR(INT((SUM(ZN(INT(SPLIT(STR([Time]),':',1)))*3600+ZN(INT(SPLIT(STR([Time]),':',2)))*60+ZN(INT(SPLIT(STR([Time]),':',3))))
%3600)%60)),2))

2. Set the number format of the calculation as

custom: ###0:00:00

 
Did this article resolve the issue?