Automation with Gantt chart sheets

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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!