Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Determine Hours from Duration

Options
Jamie Camp
edited 12/09/19 in Archived 2015 Posts

Has anyone created a sheet or formulas to calculate the montly hours from duration?  This is easy if the entire duration falls in one month but I'm struggling with calculating the monthly hours for extended durations.  For example, how many hours are allocated in each month if I have a task that starts January 11, 2016 ends July 22, 2016 and the assigned employee is allocated at 25%?

 

I have a huge sheet that contains hidden columns where I'm looking at the starting month, ending month, start year and end year.  Based on the time between the month/year I'm trying to determine which months are entiely scheduled and which ones are partially scheduled then calculate the hours allocated in each month.  This calculation is complicated becasue networkdays does not look at the non-workdays associated to the sheet.  instead, it expects an additional array of dates to define the non-work days.

 

It seems like smartsheet already does most of this in the resource allocation view.  It would be nice to dump the % allocation directly to hours.

Comments

  • JohnHinkle
    JohnHinkle ✭✭✭✭✭✭
    Options

    Dependencies will calculate duration based on working dates and your start/end dates. Parent rows will roll up the dates/duration from child rows.

     

    Create a parent row, indent all your other rows under it. Now, the dates are rolled up to a duration cell - multiply this by 8 (or whatever your working day is set to) to get total hours. 

     

    If you need to factor in allocation, this can be done in the formula too (depending on the variables) 

  • Jamie Camp
    Options

    Thanks John but I already have that.  I'm looking for a way to calculate the number of hours for all tasks in every month of the project.  Let's use the following task.

     

    Task1   01/01/16   04/22/16   100% allocation

     

    Based on my non-working day calendar this task has a duration of 81 days which is 648 hours.  That's the easy part.  I want to know how many hours this task has in each month.  This would be

     

    January = 168 hours

    February = 168 hours

    March =  184 hours

    April = 128 hours

     

    My end goal is to have a sheet/report that is similar to the Project Resource View but instead of showing the % allocation per day for each employee it would show the hours per month for each employee assigned to the project.

     

     

  • JohnHinkle
    JohnHinkle ✭✭✭✭✭✭
    Options

    Unfortunately, I don’t think that will be possible unless you manually track it...

  • Jamie Camp
    edited 11/24/15
    Options

    Yeah, this will have to be an enhacement to Smartsheet or it will be very difficult to make it generic enough to be useful.

     

    I started working on it and gave up.  it required that i create a new column for each month in the project then enter a huge formula to try to calculate the various cases you may encounter.  for example, if the project starts in November but a task does not start until December ther will be no hours in November.  Also, if the entire task is complet in November then there should be no hours for the remainder of the project.  here's the formula that I had for November.  Thre were still situations that would cause problems.

     

    =IF(OR(NETWORKDAY($[Start Date]2, IF($[proj months]$9 > $[Due Date]2, $[Due Date]2, $[proj months]$9), $[Start Date]$18:$[Start Date]$20) > $Duration2, NETWORKDAY($[Start Date]2, $[proj months]$9, $[Start Date]$18:$[Start Date]$20) < 0), 0, NETWORKDAY($[Start Date]2, IF($[proj months]$9 > $[Due Date]2, $[Due Date]2, $[proj months]$9), $[Start Date]$18:$[Start Date]$20))

This discussion has been closed.