Calculating work hours %

Options

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!

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

Answers

  • MVP OPS
    MVP OPS ✭✭✭✭✭
    Options

    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?

    best,

    Brad

    www.MVPOPS.com

  • Robert Hawkey
    Options

    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.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!