Formula to figure out % allocated

Options
TerryC
TerryC ✭✭
edited 10/17/22 in Formulas and Functions

Hi,

Is it possible to calculate % allocated based off Estimated Hours/Total Work Hours within the date range (start-end dates) of the task.

For example, Task 1's Estimated hours for completion is 10hrs and the date range is 10/17/2022 to 10/21/2022.

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @TerryC

    You could use a few functions to create calculations... for example you could use the NETWORKDAYS Function to find out how many Working Days are in your date range, then multiply this by 8 for 8 hours:

    =NETWORKDAYS([Start Date]@row, [End Date]@row) * 8

    This tells you the total hours given to that resource. You can then divide the Estimated Hours by this number in order to find the percent:

    =[Estimated Hours]@row / (NETWORKDAYS([Start Date]@row, [End Date]@row) * 8)

    So in your example above, this would return 0.25, which you could then format to be 25%.

    Cheers!

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!