Locking a cell after running formula or deleting a formula after it has run

CSears

Hello all.

I have a bit of an issue, I wrote a formula to enter todays date after a checkbox has been checked in a cell. This works great, however if I uncheck the box the date disappears.

I need the formula driven date cell to lock or hardcode the date after the formula has run once, automatically.

Current formula:

=IF(Delivered@row = true, TODAY(), "")

Someone accidentally unchecked a bunch of cells and now my dates are lost.

Thank you

Cathy Salscheider

@Paul Newcome Thanks Paul. I would just like to ask what you meant by a "copy row" automation? I was just thinking of a simple trigger when the checkbox is checked to update the desired date cell within the same row. ( I didn't have a date field available when I grabbed this snipit, but you just need one defined to pull it in.) Thanks for clarifiying.

Cathy Salscheider

I suggest using an automation instead. When the checkbox cell is check, then Record a date in the appropriate cell. Once it is done the date shouldn't change even if the checkbox gets unchecked and you do not need to lock the date cell

Paul Newcome

I agree with @Cathy Salscheider. A copy row automation is the way to go. It outputs a static date that won't change if the box becomes unchecked, and another benefit to it being a static date is that it won't change on a daily basis either. The TODAY function outputs today's date. Even if no one unchecks the box, the date will change to "today's date" every day for as long as the box is checked.

Cathy Salscheider

@Paul Newcome Thanks Paul. I would just like to ask what you meant by a "copy row" automation? I was just thinking of a simple trigger when the checkbox is checked to update the desired date cell within the same row. ( I didn't have a date field available when I grabbed this snipit, but you just need one defined to pull it in.) Thanks for clarifiying.

CSears

@Paul Newcome I was not aware the TODAY function would update every day automatically, that must be what happened and I assumed the person I am collaborating with accidentally unchecked the boxes. If you could kindly elaborate on the "copy row" automation I would appreciate it.

@Cathy Salscheider This works perfect so far, thank you!

Paul Newcome

I meant a Record a Date automation. Sometimes my fingers and brain aren't always connected. Sorry about that. I had just finished typing out an email that referred to a copy row automation and must have had a touch of auto-pilot going.