Completion Date Formula (verification needed)

[Kurt_C]

I have been using this formula: IF({Completed}=1,DATETIME_FORMAT(NOW(),‘M/DD/YYYY h:mm’),0) to record a date when a “Completed” check box is ticked off in another column.

This worked flawlessly up until recently. For some reason, all past dates are now changing to whatever the current days date is. I have jobs months old that now say they were only completed today.

Any help is appreciated. I can’t figure out what happened other than it is a new year and maybe that messed something up.

[Zollie · Accepted solution]

NOW()
This portion of your code will return the CURRENT date and time. So it might have looked like it was working when you were writing the formula, but as time passes, the timestamps will change to reflect the current time.

Unfortunately, Airtable formulas don’t allow you to cache/persist data within a formula. In other words, formulas can give you the current time, but they can’t stash that time away for later.

Solution

Going forward, I’d recommend you start using a date field instead of a checkbox for the “Completed” field. Of course, you’ll still have a ton of already completed records that don’t have proper timestamps. So before you alter the checkbox field, you might be able to get the completion dates by creating a temporary ‘last modified time’ field that only looks at the ‘completed’ checkbox field. If your checkbox records haven’t been toggled since you checked them off, it should display the proper timestamp.

[Kurt_C]

Thank you so much! The date idea instead a checkbox makes sense. I am going to take a stab at creating the temporary date modified field. I’ll admit, I have no clue where to start but I’m gonna try. Thanks again!

[Zollie]

It’s an existing field type (Last Modified Time) available when you create a new column. The configuration settings for fields of this type allow you to specify which other fields you want it watching.

[Kurt_C]

I actually found it. I messed it up by undoing it because I thought I did it wrong (it reset the last modified checkbox date), but it is OK.

Question. Can I keep the last modified field as the “completed date” field and still keep the check box that triggers that date to be recorded? Does it record that specific row’s last modification to the check box or does it record the last modification to ANY checkbox on the whole table?

[Zollie]

I wouldn’t recommend that implementation because the data might become unreliable over time. Last Modified Time looks at each row and displays the last date when a whatever columns it’s set to were altered. The problem is there’s no way for you to know if that date is when it was last toggled on or off. So if you toggle it on, it’ll display a date. But if you toggle it off again, it’ll still display a date. It’s only empty when it hasn’t been toggled. So standard user behavior could introduce bad data over time.

[Zollie]

So I’m pointing out a lot of hurdles and restrictions in using the Airtable’s standard tools and formulas. But it’s worth noting more efficient workflows are possible with the help of heavier tools. Integrations, the API, and recently a scripting block all might offer more customized solutions. Maybe check out the people in the work offered section if you want to go down that route.

[Kurt_C]

Amazing. Thank you so much for your help. I am truly appreciative of you sharing your knowledge. It’s refreshing to find people willing to take the time to help a stranger. Thank you again!