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
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

@Genevieve P. That fixed it! Thank you so much!
Help Article Resources
Categories
Check out the Formula Handbook template!