Add Column for Date Record is Closed

I want to create a new column that is a formula. The Status can be blank, Open or Closed. Once a record is closed it will not be reopened. If there is reason to do so, a new record will be opened and it will reference the original. This has only happened once. This date column should be blank unless the status of the record is closed, and when Closed, the date it was closed would appear. Am I overcomplicating this? is it as simple as an IF Statement? And yes, everything is on the same sheet.

Find more posts tagged with

Sheets

Formulas

functionality

Accepted answers

All comments

AravindGP

Hi @Sherry Fox

Any reason for the column to be using a formula and not the Record a Date workflow? You can use =IF(Status@row="Closed", TODAY()) in a date field as well, but I would suggest the workflow.

Sherry Fox

@AravindGP,

Two reasons,

1) I am an Excel girl transitioning to Smartsheet. And in my current role, I spend all day working in both.

2) OMG, a workflow will do that? I had no clue. I love the Workflows. Sometimes something is SO SIMPLE it is completely overlooked. Let me try to set that up right now! If I cannot figure it out, I shall return!

Sherry Fox

@AravindGP,

I think I did it correctly. I have never used this functionality when using Workflows, but I do like it! Here is a screenshot of what I did. Is this correct?

Andrée Starå

Hi @Sherry Fox

I hope you're well and safe!

That looks correct! Doesn't it work?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Sherry Fox

@Andrée Starå,

It has been a while. I am great, hope you are as well. I don't know if it works yet, as there have not yet been any new records that have closed. For the records that have already been closed, in order to generate a date, I used a formula to capture the modified by date if the Status was closed. Once all cells were populated, I converted the formula to values, and I am ready to have the Workflow take over completely. But at least I have a date for every closed record now, and a way to accurately have a date for future record closings. Thanks so much!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.