I’ve set up time tracking on our boards and in order to return the Time Left from the Time Allocated I have the following formula:

IF({Subitems Time Tracking}>0,ROUND(({Time allocated}-{Subitems Time Tracking}/144024/60),1),ROUND(({Time allocated}-{Time Tracking}/144024/60),1))

This works fine but returns decimal time. I’ve added another formula column to convert the decimal time to hh:mm: RIGHT(“0” & INT({Time remaining}), 2) &" h “& RIGHT(“0” & ROUNDUP(MOD({Time remaining},1) * 60, 0), 2) & " m”.

This is also working fine except when more than the allocated time has been used. For example 1 hour allocated, 1h 5 min used returns -1h 06m.

Any help on rectifying this anomaly would be greatly appreciated.

You can put your formula here and you shouldn't have that issue.

Second, the basic issue is that ROUND() always rounds numbers AWAY FROM INFINITY. So, “1.1” becomes “1”, “-1.1” becomes “-2”. One way to address this is to do something like this… Instead of: "ROUND(*formula text*)"
Use: "ROUND(ABS(*formula text*)) * IF(*formula text* >0, 1, -1)"

If you need more help, please repost your main formula using the preformatted text.

I’ve tried to work that in but I’m not having much luck. It’s the second formula that seems to be returning the error. The first formula is returning the correct value in decimal time. When I use the following formula to convert it to hh mm, it’s returning an incorrect value for negative amounts.