How can I use a calculated duration that uses my own formula?

Erik123

I want to use my own formula to calculate duration. I've added a Calculated Duration column, but I can't select that column in my project settings. What governs what column you can select for your duration column?

Genevieve P.

Hi @Erik123

You are correct: you cannot place a formula in the Duration column if you're using that column in your Project Settings for Duration (see: Areas where formula use is restricted). This is because the sheet already applies a background formula to that column - if you select a Start and End date, the Duration will populate based on the Working Days set in your Project Settings.

You can add another, regular column in your sheet to perform calculations and determine durations, but this column cannot then be selected in your Project Settings. Please let the Product team know of your use-case and why you would like to have formulas in this column by filling out this form, here.

Cheers,

Genevieve

Erik123

I have learned that if it is not a column formula that I can select my Calculated Duration as my duration column, however, the formula gets erased at that point and it just becomes a regular duration column.

Genevieve P.

Hi @Erik123

You are correct: you cannot place a formula in the Duration column if you're using that column in your Project Settings for Duration (see: Areas where formula use is restricted). This is because the sheet already applies a background formula to that column - if you select a Start and End date, the Duration will populate based on the Working Days set in your Project Settings.

You can add another, regular column in your sheet to perform calculations and determine durations, but this column cannot then be selected in your Project Settings. Please let the Product team know of your use-case and why you would like to have formulas in this column by filling out this form, here.

Cheers,

Genevieve

Erik123

Thanks @Genevieve P. . I marked your response as not answering my question, but I guess it did: it isn't possible. I can't go back and change that because once you select one it disappears, but feel free to mark it as answered by you if you wish. I did submit feedback to the linked form.

Thanks,

Erik

Erik123

FYI, I had a related, initial, question here:

https://community.smartsheet.com/discussion/88506/is-there-any-way-to-inactivate-a-task-in-smartsheet

Erik

Genevieve P.

Hi @Erik123

Thank you for providing your feedback through the link! The Product team reviews each of these submissions so it's a great way to let them know how you're using Smartsheet and any enhancements that you think would help with your current set-up.

Cheers,

Genevieve

zealvert

Hi everyone

I wish to specify the number of days in the Duration column, based on the 3-point estimation (3PE) approach, which is very crucial in my project's case.

But given the fact that we cannot use formulas in the Duration column, I'm unable to place the 3PE in it. Furthermore, if I calculate and store the 3PE in some other column, then also there's no connection between that other column and the Duration column (meaning whenever I change the 3PE data, I've got to update the Duration column manually!)

Any options / workarounds?

Thanks

Nasir

Zealvert Consulting

Genevieve P.

Hi @zealvert

You've outlined how Duration works in Smartsheet, yes. Either you can use the built-in Duration column, or have your own way of calculating duration in another column but it won't be synced to your other Project columns or Gantt chart.