Effort Driven Task

Options

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 ?


Answers

  • red1998
    red1998 ✭✭
    Options

    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
    Options

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

  • Genevieve P.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • bebe
    Options

    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.
    Options

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!