Determine Hours from Duration

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.

Find more posts tagged with

Formulas

Enhancement Request

Comments

JohnHinkle

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

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

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

Jamie Camp

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