Calculating work hours %

Robert Hawkey

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!

Genevieve P.

Hi @Robert Hawkey

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

MVP OPS

HI @Robert Hawkey

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?

Robert Hawkey

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.

Genevieve P.

Hi @Robert Hawkey

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

Robert Hawkey

@Genevieve P. That fixed it! Thank you so much!