Creating a Date completed Automation

Ethan Kohl

I am attempting to create a formula that returns the date when my "Completed" column = 100%. Smartsheet keeps saying my syntax is incorrect. From what I can tell I need to use the TODAY function paired with an addition column that acts as a check to prevent it from perpetually updating the cell. In English the formula for Date Completed column would be: If completed = 100% & check column is not checked return Today's Date. And the checked column would be: If Date Completed, is not blank, check column.
The two formulas I have as of right now are

Completed Date:

=IF(AND([Completed]@row = 100, [Check]@row = 0), TODAY(), )

and

Check:

=IF(NOT(ISBLANK([Completed Date]@row)), 1, 0)

Edit: I realize now that this is a circular reference. I think I need a third column to do some sort of storage check but IDK what exactly.

Michelle Choate 2

Is there a reason you are not using the "Record a Date" automation instead?

Michelle Choate 2

Is there a reason you are not using the "Record a Date" automation instead?

Ethan Kohl

Every time I try the automation, when it updates, it is autofilling the entire column with the same date rather than just the rows that have completed at 100%. The automation as I understand it should be something along the lines of If column "completed" =100% then record date in "Completed Date" column. I then ran the automation to the afore mentioned result.

Michelle Choate 2

try changing it to just when rows are changed as the timing and then the trigger to be when % complete changes to 100%. From this point forward, it will only do it on those rows that change. If you run it, it will do it in every row that has 100%. To prevent that add the condition that the date box must be blank