Why Can't Gantt Charts Have A Formula In The Start/End Date Columns?
I'm trying to create a Gantt Chart on a document where the End Date field is a column formula; it being a formula is pretty much non-negotiable. The formula reports a date from the 'Requested Due Date' column unless a new date is provided in the 'Extended Due Date' column. This is done to ensure that the person using the sheet can adjust the date without deleting the original requested date, which will be used later in an unrelated metadata calculation for how long tasks get delayed on average. The Start Date column is similar, being backdated from the 'Requested Due Date' based on the item type.
The issue I'm finding is that I cannot select the two Start and End Date columns because they're formula columns. I've read on the forums that you can't use formulas if dependencies are enabled, but I don't have dependencies enabled and I still can't do it. I've also read elsewhere here that you just can't do it at all and frankly don't know what to think of this.
More than anything, I'm wondering why this is even a limitation in the first place. It can do it; I know because I tricked it into doing it on this very document. I set the Start and End Dates to their corresponding columns before I made them column formulas. the result was a Gantt Chart with dynamic End Dates [meaning non-static, not a reference to Dynamic view, which we're not using].
I would be satisfied with this workaround, but now I can't open the Gantt Project Settings without the document having a fit and throwing them away, forcing me to recreate them from scratch using this same process and leaving the document in a broken state until it's fixed. This would be fine enough [if infuriating], the formulas are not complicated, were it not for the fact that the primary user of this document is not smartsheet savvy in the least and would have trouble fixing this, thus requiring somebody else to be called in to fix it on the fly. They would be an admin on this document, so they necessarily have access to the Gantt Project Settings and possibly even a reason to open them, so I can't just lock them out to prevent them from accidentally ruining it. Outside of a dire warning to never ever ever so much as look at the Gantt Project Settings [which would be sending entirely the wrong message], I see no workarounds for this. Does anybody know of one I've not considered?
Again though, more than anything I'm wondering why on earth this is even a problem to begin with. It can do it, so the decision to prevent it seems entirely arbitrary. Any idea Why? Even if there's no workaround, I just need to know why, this has been eating at me for days now.