Populate a cell with today's date when status is marked complete

Susan Peck

Hi all,

We have several task schedules that people use to track the status of their work. When the task is finished, they change the Status to Complete and the Percent Complete to 100%. We would also like to know the actual date the work was completed to compare it to the Due Date (which is referenced from another cell in the sheet that links to another schedule). For some reason, we can't get people to fill in the date field, so I'm looking for ideas.

Ideally, I would love to auto-populate the date based on the Status and Percent Complete changing to Complete and 100%. Is there a formula that would do that?

As an alternative, maybe we can change the color of the cell so it alerts the person immediately? I know how to set up this condition, but I'm not sure it would be enough of a flag because it probably wouldn't appear until they have saved the sheet and left it. I've already set up an automated reminder that comes in email, but the instructions in the email are so small (hint to Smartsheet: address the formatting of text in alerts!) that they don't read it carefully or understand it.

Other ideas?

Here is an example of the Date Completed cells that are not filled in:

Thanks for help, ideas, suggestions!

susan

Andrée Starå

Hi @Susan B Peck

Have a look at my post below with a method I developed that could work for your use-case.

More info: 

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

Would that 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 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!

John Sisounthone

Hi @Andrée Starå ,

Can you help me to figure out why my formula is not working properly?

It was only able to work in one cell but when I dragged the formula down to populate the other cells, it would either say#INVALID or #NO MATCH.

Which is strange because the sheets are exactly identical and it was able to work for the first cell that the formula was inputted.

Here is the formula that I used. =IF(MATCH([Task Name]1, {Date Allocations of Photography Calendar Range 2}), {Date Allocations of Photography Calendar Range 3})

Thank you.

Andrée Starå

Hi @John Sisounthone

Strange!

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic day!

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!

Malorie Scaramozi

I'm understanding your suggested solution for auto populating dates based on completion of the tasks up until the lookup/index/match ....

Andrée Starå

Hi @Malorie Scaramozi

Yes, something like that.

I'd be happy to share an example. 

Please send me an email at andree@workbold.com, and I'll share it with you.

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!

Samantha Labus

Did anyone find a formula for this? I am looking for this same thing!

Andrée Starå

Hi @Samantha Labus

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

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!

Mike Wilday

You can now set a date based on another cell change using an automation workflow!

https://help.smartsheet.com/articles/2481986-set-the-current-date-with-record-a-date-action?_ga=2.79950226.443393604.1609776795-1074882870.1602114816