Need a "Completed date" column: is there a "last updated" or similar formula?

[Phil_Sheard]

We’re using AirTable to track actions and filtering the todo list using a checkbox field titled “Completed”.

I’d like to setup a new view that sorts the tasks with most recently completed at the top.

Manually adding the “Completed date” isn’t an option as people will naturally forget to fill it in.

I’ve looked down the list of available formulas but nothing jumps out as being suitable.

Thanks in advance for any help.

[Tuur]

Unfortunately that’s not possible at this point in time with the standard tools…

[Hashim_Warren]

There are requests for a “last updated” thing that can be 3xposed using a formula. But it’s not here yet.

Here’s a workaround:

Instead of a check box for “done” create another table called “done”.

On the done table have two fields:

1. Formula field that pulls in record creation date
2. Linked record field that links to the task you’re marking as “done”.

On the task table you can now link to a record on “done” with the auto date.

[Julian_Kirkness]

Hi @Phil_Sheard

There is another way if you use Zapier…

1. The user completes the completed checkbox
2. This adds the record to a filtered view for completed items
3. A zap based upon a new record in that view triggers
4. It updates a field called ‘Completion Date’ with the current date

This would only work once for any given record so that any changes to the completion date would need to be done manually. Here’s the zap:

Julian

[Raymon_Geis]

Hi @Julian_Kirkness,

That’s a great workaround, thanks. I’ve been trying to work this out myself, but I can’t seem to figure out how to update the Date field in Zapier. I see you’re referencing a different field in a record (I’m guessing) in order to add the current date; do you just have another field that updates the current date/time constantly, and, when the zap is triggered, it just pulls that data from the field and adds it to the “Completion Date” field?

Thanks for your help!
Ray

[Julian_Kirkness]

Zapier has a way of entering the current date and time which is to type in:

{{zap_meta_human_now}}

It then looks like this in the Zap:

There’s a Zapier Article here:

Modifying Dates & Times

Hope this helps!

[Raymon_Geis]

Wonderful, thanks! Not yet sure if this solves my overall problem, but I will see.

[W_Vann_Hall]

Imperfect solution; however, it works entirely within Airtable and doesn’t suffer from lag time or delays possible with third-party middleware:

1. Create a new table called (for instance) [Timestamp].
2. Create a formula field in [Timestamp] called {StampedTime}. Configure the formula to be simply CREATED_TIME().
3. In your main table, define a linked records field that links to [Timestamp]. Name it whatever you would name your checkbox field: {Completed}, {Done}, {Checked}, whatever. Disable ‘Link to multiple records,’
4. Also in your main table, define a rollup field linking to [Timestamp] that rolls up {StampedTime} using an aggregation function of MAX(values). Call it something like {Completed Time}.

Now when your users want to flag task completion, rather than checking the {Completed} checkbox, they click on the plus sign in the {Completed} field. This drills through to the [Timestamp] table and opens a list of [Timestamp] records. The user then selects ‘+ Add new record’ at the bottom of the list, creating a new {Timestamp} record. The record opens in a pop-up window, which the user then closes. {Completed Time} rolls up the time at which the new [Timestamp] record was created, providing an accurate — and persistent — indication of the time at which the pseudo checkbox was filled.

Downside: Slightly clunkier than a real checkbox, requiring three mouseclicks (instead of two). As it is based upon calculated dates, some sort of mechanism is needed should manual override be required (for instance, a task is completed, but the user forgets to mark it until the next day). Mistakenly linking to an existing record results in invalid data.

Upside: Purely Airtable solution. Not constrained by third-party middleware limits, polling schedules, or prices. Fast, accurate, and easy to implement.

Bonus: Unlike the Zapier solution, if ‘link to multiple records’ is enabled, the box may be checked multiple times, and {Timestamp} will display the date and time of the most recent check. (See record #5 in the demo base for an example.)

Trick: Configure the primary field of [Timestamp] to be a formula field with a formula along the lines of

'✅            '&{Link2Main}&'-'&{StampedTime}

using the Non-Breaking Space character for the spaces following the check mark. Narrow the width of the linked record field. Now, all records with a link to the [Timestamp] table will display only a check mark.

Again, not a perfect solution — but not that bad of one, either.

Timestamp demo - Airtable

[Zac]

So clever. Even though this is not a perfect solution and won’t apply to us, I must solute your creativity.
Brilliant!

Now when your users want to flag task completion, rather than checking the {Completed} checkbox, they click on the plus sign in the {Completed} field. This drills through to the [Timestamp] table and opens a list of [Timestamp] records. The user then selects ‘+ Add new record’ at the bottom of the list, creating a new {Timestamp} record. The

[W_Vann_Hall]

So clever. Even though this is not a perfect solution and won’t apply to us, I must solute your creativity.

Brilliant!

Thanks for the very kind words.

Maybe it’s time to start using this as my sig file:

“W Vann Hall: Bending Airtable to His Will for a Year (and a Day)”

[Dakota_Bushnell]

Recently I discovered that the “Last modified time” field was rolled out.

I used this to my advantage by creating a “Completed” checkbox, and then using a formula to link to when that checkbox was checked.

The formula for my “Completed Date/Time” column is as follows:

IF({Complete Checkbox} = 1, LAST_MODIFIED_TIME({Complete Checkbox}))

I also made sure to turn off the “Use the same time zone (GMT) for all collaborators” feature so that the time reflected correctly for us, as shown below:

I hope this helps!

[Chad_Taylor]

Thank you so much, this is exactly what I was looking for! Simple and 100% inside Airtable.
Thank again.

[Sean_O_Shaughnessey]

To build on this a bit as I think new functionality makes this really easy now. Create a new field using Last Modified Time. You can have that field only look at when one field was updated (in my case Done for my Task List). Now, whenever you update the record by changing the status of Done, you have the Date / Time stamp.