Hi! Ive searched and searched, and in theory I’m doing everthing right, but I’m not getting the right response.
I have a task for “total monthly hours” and subitems where the time tracking rolls up to that top item. I created a “planned hours” budget in the image below of 12 hours, the “time tracking” shows 1 hour clocked, but I’m getting a result of -3,588 - the result returned should be 11.
This happens because the Time Tracking column is calculated in seconds, even though it shows hours on the board. Your Planned Hours are in hours, so the formula is mixing units. To fix it, convert time tracking to hours by dividing by 3600, like this: {Planned Hours} - ({Time Tracking} / 3600). This should return the correct result, such as 11 hours instead of a large negative number.
This one is a classic monday gotcha and you’re not doing anything wrong.
The issue is that the Time Tracking column does not store time as decimal hours internally. It stores time in seconds, even though it’s displayed to you as hours and minutes. Your Planned Hours column is a plain number in hours, so the formula is subtracting hours from seconds, which is why you’re seeing a huge negative value like -3,588.
Here’s what’s happening under the hood:
12 planned hours minus 1 hour of tracked time is actually being calculated as
12 minus 3,600 seconds
which results in the large negative number you’re seeing.
To fix this, you need to convert the time tracking value into hours inside the formula.
Use this formula instead:
{Planned Hours} - ({Time Tracking} / 3600)
That divides the tracked seconds by 3,600 to convert them into hours, so 1h 0m becomes 1. Then the math works correctly and you’ll get 11 as expected.
A couple of extra tips:
Make sure your Planned Hours column is a Numbers column, not Formula or Status
If you’re rolling time up from subitems, confirm the rollup is set to Time Tracking and not a mirrored value
If you ever want minutes instead of hours, divide by 60 instead of 3,600
Once you convert the units, both formulas you tried will behave exactly as expected.
