I would like to trigger a workflow based on a store opening date

vanstadenster

So for example a new store is opening on 7 October.

30 days in advance the workflow needs to kick-off, based on the Opening date, I would like to write a formula that auto-populates the start date.

This will allow me to notify people and a workflow to start.

Not sure what formula to us.

vanstadenster

I tried this but it is clearly not right

=IF(NOT(ISDATE([Opening Date]@row)), [Start Date]@row - 30 - TODAY())

Paul Newcome

Try this:

=[Opening Date]@row - 30

vanstadenster

Thanks @Paul Newcome is that the whole formula? I don't seem to be able to get this to work.

Paul Newcome

Are you getting an error or an unexpected output?

vanstadenster

It is giving me a circular reference error.

Here is a screenshot of the columns

vanstadenster

vanstadenster

@Paul Newcome I got this formula to work - =WORKDAY([Opening Date]@row, -30 to do the calculation. I think nothing worked as I am using predecessors. When I remove them it works but when I add them back th formula is no longer there.

Is there something I missing as the steps have clear dependencies. Not sure if there is a workaround if this is not possible

Paul Newcome

The circular reference error comes from putting the formula into a cell that it is referencing. You would have needed to put the formula into a different column.

You cannot use formulas in columns being used by the dependency settings. You would need to put the formula into a different column that is not being used by the dependency settings.

vanstadenster

Thanks @Paul Newcome - ok so if I put it in a different column, which I understand will work. Could it then trigger the start date to change which is used in dependency settings.

Sorry - still learning albeit a bit slow.

Paul Newcome

No. Columns being used by the dependency settings cannot be adjusted via formula or automation. You would need to leverage the API if you wanted to adjust something in one of those columns.

vanstadenster

Thank you!

Paul Newcome

Happy to help. 👍️