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!