Effort Driven Task

bebe

How do i make the end date and duration autofill, if for example i say i have 5 days effort to deliver and im 50% assigned to the task ?

red1998

I believe it would be something along the lines of this:

For End Date

=date(year([start date]@row),month([start date]@row),day([start date]@row)+([work effort (days)]@row*[% Assigned]@row)))

For Duration

=[end date]@row-[start date]@row

Let me know if that works!

bebe

Thanks, but i couldnt figure out how to add a formula to either of those cells/columns.

Genevieve P.

Hi @bebe

It sounds like you have this sheet set as a Project Sheet, which means you won't be able to enter formulas into specific columns (as it's already using formulas to auto-populate end dates based on Start Date and Duration).

See: Areas where formula use is restricted

You'll want to create your own regular Date type of column for the End Date formula above and a new text/number column for the Duration column. Neither of these columns should be used in your Project Settings. This way you can customize the content showing based on what you want to see.

Cheers,

Genevieve

bebe

hi, thanks Genevieve, Im quite new to SS so not sure what you mean by a Project Sheet, do you mean a sheet in Gantt view? If so then yes. (ie like MS Project, Omniplan, others)

I created a New End Date and New Duration Column in order to just be able to put a formula in them, but then you cant set it in Project Settings to use those new columns right? and also it then wont drive the Gantt view either right ?

Genevieve P.

Hi @bebe

Within your Gantt sheet, there is a window you can open up called Project Settings:

If you have Dependencies enabled (as I do above, with the blue check mark) then there are some restrictions on your Project Columns because now they're being used to automatically adjust dates and content in your sheet.

The Duration and Start Date then dictate the End Date for that row. A Predecessor relationship now changes when a dependent row starts, etc.

This means that you cannot place a formula in the Duration column or the Date Columns because they're being used by the Project Settings Dependencies, instead. (See: Project Sheet Columns: Start Date, End Date, Duration, % Complete and Predecessors)

However! If you're simply using the Gantt view in your sheet, then you don't necessarily need Dependencies enabled, see: Create and work with a Gantt chart

Un-checking the "Dependencies enabled" box will then all you to add formulas to your columns.

Then these columns can still be used in a Gantt chart, as a Gantt view only needs 2 Date Columns. What you won't have is the link between rows (Predecessors / dependent tasks) or the summary rollup on Parent rows.

Let me know if this helps clarify!

Cheers,

Genevieve