Calculating work hours %
I feel like this should be very simple but my math just isn't checking out. I have a duration column tracking how many weeks per task the work will take. I also have a "work hours" task that is tracking how many actual hours the work will take over that given duration. I'm trying to make it so my "Allocation" column calculates the total % of work hours compared to the total duration. So for example:
Task A
Duration = 1week
Work Hours = 8
Allocation should be 20% but when I'm trying to set up a column formula =[Work Hours]@row / (Duration@row * 40) it's coming out to 4%.
I'm clearly not great at math so can someone please let me know what I'm doing wrong?
Thanks!
Best Answer
-
Are you using the Duration column in a Project sheet, and putting in "1w"?
If so, although the display value show "1w", the underlying data in numerical values is seen as the number 5 (since 1w is equal to 5 working days).
You can test this with a formula:
=Duration@row
I would suggest multiplying by 8 (for 8 hours) instead of 40.
=[Work Hours]@row / (Duration@row * 8)
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
not totally sure here without more info, but looking at what you have provided the math should calculate properly. can you attach a screen shot of your formula and your columns?
best,
Brad
www.MVPOPS.com
-
I can't screenshot because of NDA but the information I provided is exactly what I have in the sheet.
Duration column
Work Hours column
Allocation column
Duration is set to 1w
Work Hours is set to 8
Allocation column formula is =[Work Hours]@row / (Duration@row * 40) and the result is 4% for some reason.
-
Are you using the Duration column in a Project sheet, and putting in "1w"?
If so, although the display value show "1w", the underlying data in numerical values is seen as the number 5 (since 1w is equal to 5 working days).
You can test this with a formula:
=Duration@row
I would suggest multiplying by 8 (for 8 hours) instead of 40.
=[Work Hours]@row / (Duration@row * 8)
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@Genevieve P. That fixed it! Thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!