Time remaining formula returning incorrect values where more than allocated time has been tracked

[PenelopeTwist (Penelope)]

Hi Monday.com users

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.

Thanks
Penelope

[JCorrell (Jim - The Monday Man)]

@PenelopeTwist

Hello and welcome to the community!

First, it doesn’t look like you formula text in your post was formatted correctly.

If you use the “Preformatted Text” feature above:

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.

---

Jim - The Monday Man (YouTube Channel)
What is Make & How can it help you with monday?
We Create Custom Solutions - Your Make or Ours
Schedule a 1-on-1 Tutorial Session (for monday, Make or Google Sheets)

[PenelopeTwist (Penelope)]

Thanks @JCorrell

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.

RIGHT("0" & INT({Time remaining}), 2) &" h "& RIGHT("0" & ROUNDUP(MOD({Time remaining},2) * 60, 0), 2) & " m"

[JCorrell (Jim - The Monday Man)]

@PenelopeTwist

Try this:

IF({Time Remaining} < 0, "-", "") &
TEXT(INT(ROUND(ABS({Time Remaining}) * 60, 0) / 60), "00") & " h " &
TEXT(MOD(ROUND(ABS({Time Remaining}) * 60, 0), 60),"00") & " m"

---

Jim - The Monday Man (YouTube Channel)
What is Make & How can it help you with monday?
We Create Custom Solutions - Your Make or Ours
Schedule a 1-on-1 Tutorial Session (for monday, Make or Google Sheets)

[PenelopeTwist (Penelope)]

@JCorrell you are already at legend status in my books

That worked a treat. Thanks so much for your help.

[JCorrell (Jim - The Monday Man)]

@PenelopeTwist

LOL… thank you!

You are welcome.