I am hoping to find a way to calculate and display hours remaining. Essentially we are using the board to track hours worked on each project (projects are rows, hours are columns). We would like to be able to calculate and display remaining hours based off of our total minus each row entry. I know I could display percentages however those don’t automatically calculate the remaining hours unless the remaining hours are manually entered.
Hi Kayla!
I would love to help you with your formula. Can you share a screenshot of your board or the columns that you have?
Thank you!
Shanine | Solution Consultant and monday.com expert
www.mindflows.eu
Hello! Here is an image of the columns I am looking at. I am trying to be able to display in a chart the amount of hours per project. Currently I can do that, however I have to manually input the remaining hours (out of 80) instead of it automatically populating how many hours are used out of the 80.
Hello @kayla061819,
Sure thing! You’ll want a formula to calculate the remaining hours by subtracting the sum of hours worked from the total hours. iadpworkforcenow.com
Here’s a simple way to do it in Excel:
- Assume your total hours are in column B and hours worked are in column C.
- In column D, use the formula
=B2-SUM(C2:C5)to calculate the remaining hours for each project.
Let me know how it goes!
Best Regards,
Ellen Hogan
Thank you for contributing! The hope was to be able to do this in Monday.com!
To calculate remaining hours, you can create a formula that subtracts the sum of hours worked from your total hours. Try this:
plaintext
Copy code
{Total Hours} - SUM({Hours Worked})
This will display the remaining hours automatically for each project.
Hi Kayla,
Based on my understanding, you’d like to track the remaining hours for all projects by comparing the total hours versus the hours already spent. To achieve this, I suggest setting up two connected boards:
- Projects Board: Where each project’s progress and hours are tracked.
- Allocation of Hours Board: This board will store the total hours allocated for projects
We’ll connect these boards and mirror the hours spent in the Projects Board onto the Allocation of Hours Board. Then, I’ll add a formula column in the Allocation of Hours Board that will subtract the mirrored “hours spent” from the “total hours” allocated, giving you a clear view of the remaining hours, similar to the screenshot attached.
Let me know if this aligns with what you had in mind
Hello, this is a great start, thank you for your help! This works great for the first row, however the goal is to keep subtracting for each subsequent row. Ideally row one would have a total of 80, remaining hours of 67.25, hour this billing period of 12.75, and then the next total in row 2 would say 67.25, followed by hours remaining showing 64.25, accounting for the hours billed in row 2. Does that make sense? Ideally I could subtract with each row so the total and remaining hours are changing with each column entry of the hours this billing period column.
Hi @kayla061819,
The formula column can only reference columns from the current item, so you won’t be able to achieve it this way. It could be done with a 3rd-party app called the Advanced Formula Booster which does not use the formula column at all and can reference any item in any board.
Here is a demo:
This is achieved with a simple formula:
and 2 automations:
