How to get total sum of data in a column format dd:hh:mm

user09981

Need to get total of a column with data are formatted as dd:hh:mm

Valiant

If I understand correctly, you're looking for a way to sum the time values within a column that are currently formatted to show dd:hh:mm. I broke it down by converting each section to minutes and then extracting the amount of days, hours and minutes in the final "minute sum".

 

This beast mode seems to work in my testing. Just replace the 'Time' column throughout the code with whatever column you are wanting to sum. Hope this helps!

 

CONCAT(
FLOOR(SUM(SUBSTRING(`Time`,0,INSTR(`Time`,':'))*24*60
+
SUBSTRING(`Time`,INSTR(`Time`,':')+1,INSTR(`Time`,':'))*60
+
RIGHT(`Time`,2))/1440)
,':',
FLOOR((SUM(SUBSTRING(`Time`,0,INSTR(`Time`,':'))*24*60
+
SUBSTRING(`Time`,INSTR(`Time`,':')+1,INSTR(`Time`,':'))*60
+
RIGHT(`Time`,2))-(FLOOR(SUM(SUBSTRING(`Time`,0,INSTR(`Time`,':'))*24*60
+
SUBSTRING(`Time`,INSTR(`Time`,':')+1,INSTR(`Time`,':'))*60
+
RIGHT(`Time`,2))/1440)*1440) )/60 )
,':',
SUM(SUBSTRING(`Time`,0,INSTR(`Time`,':'))*24*60
+
SUBSTRING(`Time`,INSTR(`Time`,':')+1,INSTR(`Time`,':'))*60
+
RIGHT(`Time`,2))
-
(FLOOR(SUM(SUBSTRING(`Time`,0,INSTR(`Time`,':'))*24*60
+
SUBSTRING(`Time`,INSTR(`Time`,':')+1,INSTR(`Time`,':'))*60
+
RIGHT(`Time`,2))/1440)*1440)
-
(FLOOR((SUM(SUBSTRING(`Time`,0,INSTR(`Time`,':'))*24*60
+
SUBSTRING(`Time`,INSTR(`Time`,':')+1,INSTR(`Time`,':'))*60
+
RIGHT(`Time`,2))-(FLOOR(SUM(SUBSTRING(`Time`,0,INSTR(`Time`,':'))*24*60
+
SUBSTRING(`Time`,INSTR(`Time`,':')+1,INSTR(`Time`,':'))*60
+
RIGHT(`Time`,2))/1440)*1440) )/60 )*60)
)

user09981

Thank you ValiantSpur this is close to what I need.

Is it possible that in my chart, the label and the legend will be formatted as dd:hh:mm ?

Please see the attached file.

 

Appreciate your help.