Using Formulas for Start Date/End Date in Project

rdburdick315

Hi Smartsheet community,

I am working on creating an automated Gantt chart based off another sheet that I am using to run an Agile project. It is not going well.

My 'source' sheet is a grid that shows each of our deliverables and iterations. I have used an index-match formula to bring that data into the Gantt chart so the Iteration can be pulled from the source sheet automatically. This works fine.

I turned predecessors off on the Gantt (project) sheet, and put this formula into the "Start Date" column:

=IF(Iteration@row = "April Release i2", DATE(2022, 4, 15), IF(Iteration@row = "April Release i1", DATE(2022, 4, 1), IF(Iteration@row = "May Release i1", DATE(2022, 5, 1), IF(Iteration@row = "May Release i2", DATE(2022, 5, 1)))))

This also works (same logic for the Finish column as well)! The problem I have is when I do this, it turns the sheet into Grid view. When I turn back to Gantt view, I get an error stating "A sheet must contain at least two date columns to display the Gantt view." However, I have to date columns and specifically used the Date function to avoid this issue. The output of this formula is a date and the column still is categorized as a date column.

Does anyone have any experience with this? I've tried doing that formula in a hidden column and then pulling from that into the Start/Finish columns but that doesn't work either. Is this a possible request enhancement?

Thanks for reading and providing your feedback.

Paul Newcome

If you have them set as column formulas that could be the issue. Gantt charts do not currently recognize columns with column formulas.

Paul Newcome

If you have them set as column formulas that could be the issue. Gantt charts do not currently recognize columns with column formulas.

rdburdick315

Thanks Paul, that worked. I went back to cell formula and manually dragged the formula down the column. Glad to see this works!

Paul Newcome

Happy to help. 👍️