Populate a cell with today's date when status is marked complete
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.
Here is an example of the Date Completed cells that are not filled in:
Thanks for help, ideas, suggestions!
Help Article Resources
Check out the Formula Handbook template!