I noticed that when using GANTT view, the duration column calculates specifically to number of days between star date and end date including the start and end dates. Some other options for calculating duration like the number of Mondays from the start date or number of business days (user specified what days count) between dates would be interesting ways to calculate duration. This would work well especially with my next suggestion below.
Having a lock ability where one of the 3 columns (Start Date, End Date, Duration) can have the value locked would create a nice dynamic. For example, if I know something will take 10 days and the start date changes, I can lock the duration and enter the new start date and the end date will get adjusted automatically similar to sliding the task in GANTT view. Another example would be locking the End Date as a set deadline and then adjusting the duration which automatically gives you the start date.
The last suggestion I have might be a bug but relates to this. When a row is created from a form or moved to a sheet from another, the GANTT column for duration does not auto-populate until the GANTT column start date or end date is edited. This ends up defeating the purpose of the GANTT view because the tasks do not appear in the view until an edit is made to either date.
One way to implement your “locking” idea would be related to the use of formulas within PM columns (if/when that is a possibility) … if a cell contains a formula, that could be the queue to SSDC to avoid automatically updating that particular value. For example, if you want to define the start and end dates, then the user can make the duration a formula (either using calendar days or WORKDAY). Or if you want the end date to be fixed, make it a formula (even if it is just =DATE(y,m,d)). Anyway … this is just an idea for SSDC as a way to implement the idea of allowing the user to define how the dates/duration update.
And while I’m at it … a small detail - it would be convenient if there was a named range that a user could use within the WORKDAY, WORKDAY.INTL, NETWORKDAYS, NETWORKDAYS.INTL functions that would correlate to the work days chosen in the PM settings.
And another plug for future conditional formatting options … it would be nice if one of the conditional formatting options could be something like “ISFORMULA” so that you could change the background color or font color automatically if a cell contains a formula - to help prevent overwriting formulas in columns where you may have a mix of formulas and manually entered date (common with gantt charts).
Thanks for calling out the issue with duration value not updating when a new row is submitted via a form. This should happen automatically. We’re tracking this as BUG-004490.
We are also (tentatively) planning a set of enhancements to Project Management columns allowing you to use formulas in them (as long as dependencies are not enabled).
Conditional formatting is imminent. Stay tuned for an announcement about this soon.