Add Column for Date Record is Closed

Options

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.

Sherry Fox

Business System Data Analyst

Del-Air Heating, Air Conditioning, Plumbing and Electrical

EAP | Mobilizer | Automagician | Superstar | Community Champion

https://www.linkedin.com/in/sherryfox/

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭✭
    Options

    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.

    Thanks,

    Aravind GP| Principal Consultant

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Options

    @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

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Options

    @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?


    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Sherry Fox
    Sherry Fox ✭✭✭✭✭✭
    Options

    @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!

    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

    https://www.linkedin.com/in/sherryfox/

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!