Automation with Gantt chart sheets

Vadim K

I have a sheet created from project plan with Gantt chart template. It has naturally duration, start and end date columns. I added baselines to it.

I also have Status column and '% complete' column.

I am setting up a set of automations that keep all 4 columns in sync:

• if status is complete - % complete must be 100
• if user sets % complete to 100 - to change the status to complete
• If the status is Complete - set the end date to today
• If the status changes from 'Not Started' to 'In Progress' - set start date to today

It looks like I made first two working (I added even a filter to consider only rows without children). But when I try to use RecordDate automation I receive the error 'No date columns available'.

Hence, I cannot configure last two automations.

Initially I thought it is date/time vs date related. If I create a test Date column (it seems I cannot create date/time column) I can see that test column available for automation. Columns that came from a template and their base line counterparts are date/time, but I set 'date only' checkboxes on all 4 - still not visible by automation.

As far as last automation - is it possible to configure that in the first place? Checking for status 'In Progress' is not enough - as it could move from 'Complete' back to the 'In Progress', in which case I don't want to set a new start date.

Devin Lee

Hey @Vadim K

Smartsheet has a history of issues when it comes to time/date. For a long time date/time columns were not a thing and only obtainable through templates. I don't think there is a simple fix to your issue and on top of that if you read in the record a date page from Smartsheet it states the below information.

• Record a date cannot set start and end dates that are automatically calculated using predecessor or parent rollup dependencies.

https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action?_ga=2.24582458.1652085972.1637963728-1498618058.1635650848

You might be able to solve your issue via IF formulas and another date column.