Creating a Date completed Automation

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.

Find more posts tagged with

Accepted answers

Michelle Choate 2

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.