Add 2 duration fields (only hour and minutes) and display result as hh:mm

December 17, 2019 13:35

Hi there,

I'm trying to add up 2 calculation fields and display the results in the format "hh:mm". Can anyone help me with that please?

Thanks a lot.

Comments

14 comments

Rainer Grabowski

December 17, 2019 17:08

Hi Fernando,

var sum = @H.E. (h) 60% + @H.E. (h) 100%;
var h = parseInt(sum);
var m = 60 * (sum % h);
("0" + h).substr(-2) + ":" + ("0" + m).substr(-2)

Rainer

Fernando Falcão

December 21, 2019 13:44

Edited
Rainer Grabowski, I don't know what happened. I created some tests and only one of them calculated right (see print):

The other print is how I put the formula you gave me:
0

Comment actions Permalink
Rainer Grabowski

December 25, 2019 15:45

Edited
Fernando Falcão

That cause the var m returns in 3 cases a decimal and substr() gets the latest 2 digits of the decimals. I've added one line (another var m) which rounds the result of the first m.
```
var sum = @H.E. (h) 60% + @H.E. (h) 100%;
var h = parseInt(sum);
var m = 60 * (sum % h);
var m = Math.round(m,0);
("0" + h).substr(-2) + ":" + ("0" + m).substr(-2)
```
Rainer
0

Comment actions Permalink
Fernando Falcão

March 02, 2020 17:17
Rainer Grabowski. I just tried adding the new line as you told, but it didn't work aswell.

Sorry for bothering you again. XD
0

Comment actions Permalink
Rainer Grabowski

March 03, 2020 14:49
For me it works, see screenshots:
0

Comment actions Permalink
Fernando Falcão

March 04, 2020 14:52
OK, So it's probably something on my end. I'll try to figure it out. Thanks a lot!
0

Comment actions Permalink
Fernando Falcão

April 15, 2020 14:59
Rainer Grabowski, Would you help one more time please? The formula you gave me is working just fine now. But there are some cases that still need to be fixed, for example when the filed used on the calcutation is emty:

It heappens when 1 or both blanks in the Duration field is empty:
0

Comment actions Permalink

Rainer Grabowski

April 17, 2020 09:09
Edited

Hello Fernando,

great to hear that it works for you. The following code returns
00:00 if both are empty or if only one it filled the hour:min of that one:

var he60 = @H.E. (h) 60% || 0;
var he100 = @H.E. (h) 100%; || 0;
var h = parseInt(sum);
var hx = h == 0 && sum > 0 ? 1 : h;
var m = 60 * (sum % hx) || 0;
var m = Math.round(m,0) || 0;
var hLen = h.toFixed().length;
var hLen = hLen < 2 ? 2 : hLen;
("0" + h).substr(-hLen) + ":" + ("0" + m).substr(-2)

Rainer

Fernando Falcão

April 20, 2020 13:21

Edited
It's not working:

I also tried taking the ";" off the second line of the formula (marked in red), then the error is different: "sum is not defined".
0

Comment actions Permalink

Rainer Grabowski

April 21, 2020 13:48

Sorry for the typo and the forgotten sum line:

var he60 = @H.E. (h) 60% || 0;
var he100 = @H.E. (h) 100% || 0;
var sum = he60 + he100;
var h = parseInt(sum);
var hx = h == 0 && sum > 0 ? 1 : h;
var m = 60 * (sum % hx) || 0;
var m = Math.round(m,0) || 0;
var hLen = h.toFixed().length;
var hLen = hLen < 2 ? 2 : hLen;
("0" + h).substr(-hLen) + ":" + ("0" + m).substr(-2)

Rainer

Fernando Falcão

April 24, 2020 17:50
Now it works perfectly! But it won't display as an option to make reports. Maybe the format are not being recognized as hour/time:
0

Comment actions Permalink
Rainer Grabowski

May 03, 2020 11:56
It's not displayed cause the result of that field is text - and you can't do math operations with text. For reports you would need another, maybe hidden, field which calculates the result as a number - text: 2 hours 30 minutes = number: 2.5 hrs.
0

Comment actions Permalink
Fernando Falcão

May 05, 2020 14:39
I imagined that was the case. Is there a way to set the formula to display the info as number/hour = 2.5 hrs?
0

Comment actions Permalink

Rainer Grabowski

May 08, 2020 18:05

Sure.

var he60 = @H.E. (h) 60% || 0;
var he100 = @H.E. (h) 100% || 0;
he60 + he100

Rainer

Please sign in to leave a comment.

Comments

Didn't find what you were looking for?