Using Formulas for Start Date/End Date in Project

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.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!