Changing Duration to Hours changes my Finish time

Lorraine Morris

Hi all,

I am having a problem that needs your assistance.

Most of our projects are done in hours over a number of days.

I have changed the Duration to hours instead of days in my sheet but what it then does is change the Finish time.

How have you corrected this?

Your assistance would be greatly appreciated

Find more posts tagged with

Sheets

Support

Comments

J. Craig Williams

For all, I have more information on the problem from Lorraine:

She changed

the duration per task into hours but what that has resulted in is a change in my actual finish times.

Further, she wants her

dates remain the same for instance a task will take 32 hours over 5 days.

Lorraine,

This is a common misunderstanding between effort and duration.

The [Duration] column is just that - how long with the task takes, from start to finish, in clock-time for working hours.

For an 8 hour work day, a 5d or 40h task DURATIONs are identical.

There is another column available (or can be - it is configurable) to show EFFORT and that is usually named [Allocation %]. Tasks in Smartsheet default to 100%.

This is used when Resource Management is enabled.

That 5 day task above at 100% allocation also requires 40 hours of EFFORT.

However, you expect the person to only devote 32 hours to the task, leaving 8 hours to do something else.

In that case, the [Allocation %] should be set to 80%.

Your DURATION is the same (5 days) but the resources used will only be 32 hours.

Your Resource View (if you have that feature available and enabled) will show 80% allocation for the 5 days and your duration will still be 5 full days.

In other words the resource loading will be 6.4hours per day, not 8-8-0-8-8 or some other combination.

You'll need to update your sheet to the original durations and calculate your allocation %. Unfortunately, the Allocation % column does not accept a formula if your Dependencies are enabled.

You could try this:

1. Create a column called [Hours]

2. In that column put in your effort in hours - as a number

2 is two hours, 16 is sixteen hours, etc...

3. Create a column called [Calced Allocation]

4. In that column, add this formula

=Hours23 / (Duration23 * 8)

for row 23.

This will return a number where 1 = 100%.

You can format it as a percentage to see 100% instead of 1 if that helps.

This formula takes the number of effort hours and divides by the value of your [Duration] column. The value in the [Duration] column is in days, so it needs to be multiplied by 8 (working hours / day) to convert to hours.

Once this is calculated, you can use the value to update your actual [Allocation %] column.

Hope that helps.

Craig

Lorraine Morris

Thank you its worked! I have a follow up questions, in a week/day can resource be 150% busy or does that mean that one of my calculations is incorrect?

J. Craig Williams

Lorraine,

Yes, you can overbook someone. Pretty easily from my experience.

If you have Resource Mangement available and enabled, you will see a little red meeple in the info column (the one that looks like a fancy italics i ).

If you have Leia assignd to a week long task (5 days duration) and 50% allocated, and then give her a task on Tuesday all day at 100% allocated, she's 150% allocated on Tuesday and the system will warn you.

It won't DO anything like shift task dates or refuse to allow you to assign the task to her, just provide you with the warning.

If you click the meeple, it will take you to the Resource View and you can determine what the conflict is, if the Tuesday assignment was on a different sheet.

Hope that helps.

Craig