Checkmark in one column to show a completed date on a second column and it stays that date

SRenner · 11/13/18

I am looking for a formula that when I checkmark a box (to show a task completed) the column next to the checkmark will auto populate the date the box was checkmarked. I also want it to stay that date unless that row or checkbox is modified/unchecked.

I got as far as the date populating but the next day it is still the current days date rather than the previous day on which the box was checked.

any help and advice would be appreciated.

Thanks

SRenner

Andrée Starå · 11/13/18

Hi,

Unfortunately, it's not possible natively in Smartsheet today. It can be done with the help of third-party tools like Zapier or similar.

I hope this helps you!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

SRenner · 11/13/18

Thanks Andree,

I am currently using =IF([Phone Activated?]12 = "true", TODAY(), "") and it gives me todays date.

the issue is when I go back in tomorrow, it will be tomorrows date instead of the day I checked it on.

I thought there was something that we could add to mark the date it was checked and keep it that date? is that correct? just looking for clarification before looking at third parties.

thanks

Andrée Starå · 11/13/18

Happy to help!

TODAY will always show the current day. You can look at the Cell History or the Activity Log to see when it was checked.

Would that work?

Best,

Andrée

Paul Newcome · 11/13/18

I also want it to stay that date unless that row or checkbox is modified/unchecked.

All you need is a Modified (date) column. It tracks the date and time that row was last modified.

Andrée Starå · 11/13/18

Paul,

I would advise against it or at least do it with caution because it's easy that something changes on the row and then that date isn't correct anymore. You can, of course, check the history so if that's ok it' a great option to be able to see the date.

Best,

Andrée

Paul Newcome · 11/13/18

Andree,

I suggested that because the original post specifically stated what is in bold. They want it to update if the row is changed or the box is unchecked.

I normally try to avoid the Modified (date) column because you can't stop it from updating if a change is made, but in this case it sounds like that's exactly what is being looked for.

Andrée Starå · 11/13/18

Sorry! I read that wrong. You are correct!

Paul Newcome · 11/13/18

Haha. No worries. I had to re-read it a few times before my first post because I thought I had read it wrong at first. It's usually the exact opposite that people ask for.

@SRenner

Thanks for keeping us on our toes and not letting us just run on autopilot. We need that around here sometimes.

SRenner · 11/14/18

Yes thanks for your comments they definitely help!

I have a modified date column that I want to make the date stay (timestamp) only when I check the box.

for example. the phone was activated on 11/10/18 and I check the box on that day. when I come back on 11/11/18, the date is now 11/11/18.

I want it to stay 11/10/18 which is when I checked the box.

likewise if I uncheck the box and then check it again, it will update to the time I last checked the box. below is a screenshot of what im wanting to do.

I have installed phones on different dates and checkmark the date as I do them, and would like to know quickly when those phones when live.

Thanks for all the help.

SRenner

smartsheet formula question.JPG

Paul Newcome · 11/14/18

Ah. Ok. So you ARE looking for a time stamp. Unfortunately that makes Andree's first response the case. Smartsheet does not currently have a readily displayed timestamp feature outside of the Created and Modified columns.

You would have to manually enter the date to keep it from changing once you check the box. If you would like the checkbox for quick reference, you could manually enter the date and automate the checkbox to say

=IF(ISDATE([Date Column Name]@row), 1)

This will check the box once you enter a date.

SRenner · 11/15/18

ok that would work for me. thank you all for the help!!

Andrée Starå · 11/15/18

I'm always happy to help!

Have a fantastic day!

Best,

Andrée

Paul Newcome · 11/15/18

No worries. I'm glad we were able to get something figured out for you.

Laura Bell · 10/12/20

@Paul Newcome @Andrée Starå Is this still true? There is no "date stamp" capability in Smartsheet? I am looking for something very similar to this question. When something is checked complete, I wanted dated and that date to stay the same.

Thank you!!

Andrée Starå · 10/12/20

Hi @Laura Bell

Yes, it is, but the feature is coming.

In the meanwhile. Please have a look at my post below with a method I developed.

More info:

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

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 help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Checkmark in one column to show a completed date on a second column and it stays that date

Comments

Categories