Forum Discussion

lmatera's avatar
lmatera
Frequent Visitor
10 years ago
Solved

Sum hours

Hello!   I need to sum hours (like in the pic) but I haven´t been able to develop it...   Total: 49:00   Anyone? =)   Thanks!
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    Some variation of this should work for you, this formula converts the text below into minutes and divides by 60 to get hours.

     

    This is a DAX custom column

    =(LEFT([EST Block time],FIND(":",[EST Block time])-1)*60+RIGHT([EST Block time],LEN([EST Block time])-FIND(":",[EST Block time]))*1)/60

     

    If you want it in the format below, you would have to have to do something like:

     

    DAX

     

    Custom Columns

    [Hours]=LEFT([EST Block time],FIND(":",[EST Block time])-1)*1

    [Minutes]=RIGHT([EST Block time],LEN([EST Block time])-FIND(":",[EST Block time]))*1)*1

     

    Custom Field

    Sum:=CONCATENATE(SUM([Hours])+ROUNDDOWN(SUM([Minutes])/60,0),":",MOD(SUM([Minutes]),60))

     

BrunoN's avatar
BrunoN
Regular Visitor
6 years ago

Hi,
For those who need to have the result in this format but summarizing seconds. (you can either convert the time to seconds and it will work fine)
In order to Sum time in Seconds, converting it to the total sum in the format Hours:Minutes:Seconds
Where the Total Duration(s) is the Sum of the duration column in seconds


Total Duration(time) =
var Hi=divide([Total Duration(s)];(60^2))
var H=INT(Hi)
var Mi= (Hi-H)*60
var M = int(Mi)
var S = round((Mi-M)*60;0)
Return
If(H+M+S=0;
BLANK();
(H&":"&M&":"&S))

note: This formula will deliver the sum of seconds in the format:
HH:MM:SS
as the total number o hours like, p.e. 125h32m17s
where each 60seconds = 1min, 
each 60min = 1hour
hour: Sum of the total hours.