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)
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