Timestamp for when checkbox has been checked

There currently isn’t a mechanism that I’m aware of that can provide a timestamp (without manual entry), or otherwise bring part of the history log into the record itself.

The quickest solution would likely be the “Last Modified” timestamp that other users have asked for:
https://community.airtable.com/t/created-modified-timestamps/35

The other way to go about this (but would require significant changes in your table structure) is to change the individual checkboxes into a linked list of “Interactions”. You could then display the most recent interaction for each ticket:

Of course, with this latter approach, it actually becomes harder to calculate the time between interactions, because although there is a function to get MIN(value) or MAX(value), i.e. the first and last interactions, there’s no way to reference a previous/next interaction.

I’m pretty sure you can’t do this inside Airtable - but if you have Zapier then you could create a Zap with a trigger of New Record in View and a view which is filtered on Followed up = true. This zap could then update the record and record the time. The only issue is that Zaps don’t run immediately so the time recorded could be either 5 or 15 minute later than the update (depending on which Zapier account you have) - whether this is an issue depends on whether this delay is significant to your timescales.

In general, I find Zapier to be an excellent tool to automate things in Airtable - with the exception of waiting for the zap to run. Hopefully Airtable will enable Webhooks eventually which would then mean that Instant Zaps would be possible.

Julian_Kirkness’s method is in essence what we do, although we have a slightly different method:

1. Create view which only shows records with the relevant checkbox checked:
2. Create a Zap in Zapier which is triggered by new records in the view from Step 1. The action is to create a new record in a separate table. (So, Airtable is both trigger and action of the Zap.)
3. Create a column in the new table of the “Created Time” type. You will then know (within 15 minutes, for the typical Zapier subscription) when the checkbox was checked based on the time the new record was created by Zapier.

I couldn’t get the Zap to work the way you described it (I could trigger correctly and use grab hold of the trigger to do the action, but couldn’t figure out how to put in a formula like NOW() or TODAY() into the action step). Perhaps it is an issue with the field type. If you know how to do it (i.e, what field type to use and what formula to use), please let me know.

However, I did get it to work inside Airtable itself with the following formula in the Follow Up Time field:
IF({Followed up}=1,DATETIME_FORMAT(SET_TIMEZONE(NOW(), ‘America/New_York’), ‘M/DD/YYYY LT’),"")

Actually I take that back - my formula doesn’t quite work because it just populates everything with a checked box to the current time. Let me know if you can get the action step to work.

I was able to get my checkbox to trigger a timestamp.

IF({Done}=1,DATETIME_FORMAT(NOW(),'M/D/YYYY'),0)

IF({Done}=1,DATETIME_FORMAT(NOW(),'M/D/YYYY'),0)

This works for me, very handy thanks.

How precise should the time be when using:

IF(COMPLETE,DATETIME_FORMAT(SET_TIMEZONE(NOW(),‘America/Chicago’) ,‘M/DD/YYYY h:mm A’),"")

I’m trying to get a current time stamp (including h:mm), but the Completed Time is updating all rows every 5-7 minutes or so.

IF({Done}=1,DATETIME_FORMAT(NOW(),'M/D/YYYY'),0)

Does this not update upon base-refresh? Killing the viability of persistent timestamps? Maybe I’m misunderstanding the NOW function in Airtable VS. Google Sheets…

You are right: Currently there is very little persistence within Airtable. Pretty much the only immutable field or function is CREATED_TIME(). If you can tie an action to the creation of a record, you can hold a timestamp; otherwise, it’s very difficult.

I know nothing about Zapier and am trying to timestamp a record when a box is checked. Can you give a more step by step instruction on this. Specifically, which record do you select in the setup phase of the action in Zapier? Where and what coding do you need to put in the timestamp , NOW() or DATETIME_FORMAT (NOW(),h-m-s-DD/MM/YYY), etc.

I hate to be a pain, but I am a real newb and need all the help I can get!

Thanks

Jacob

Im not sure if you still need this, but i was working on a similar issue. This is the code i used, the Field {uploaded} is a checkbox column that generates the timestamp. By default is had the time and date on it but you turn the date off by clicking on the Formatting tab in the formula box.

IF({Uploaded}=1,TODAY())

Regards

Paul

Paul,

I don’t think your Timestamp is going to work the way you intend. The TODAY() part of your formula is always going to evaluate to, literally, today’s date, when your base is reloaded and {Uploaded} evaluates as “1”, or “true”.

Your timestamp is going to change on a daily basis. If you had any timestamps created yesterday, and you reload your base, I’m pretty sure you’ll see them all timestamped for today, not yesterday. TODAY() is not a static formula, and the value that populates in the cells of that field will not be static either.

Yep i just realized that!

Ya, unfortunately, there’s still not a good automated way to mark a record with a “last edited” timestamp, which is what most people are looking for. It can be done manually, but an automated solution is best.

Right i gave up! I’ve wrote an external script to populate it. :slightly_smiling_face:

I still haven’t cracked this… And we need to!

At it’s simplest, the brief is:

1. Check checkbox A, creating a value of 1.
2. Adjacent formula field B is then. populated by a timestamp, inclusive of both date and time, matching step. A true timestamp.
3. If checkbox A is unchecked, field B is made empty, a value of 0.
4. If checkbox A is RE-checked, field B is repopulated with a NEW timestamp matching the time of step 3.

HOW, is this not possible?!

What sort of external script? Zapier?

If so, mind sharing? I have several Zapier-based POCs running and none address this issue accurately. We simply require CHECK = EXACT TIME CHECKED, NO CHECK = NOTHING, RECHECK = NEW TIME CHECKED, time is always accurate to the minute checked. And PERSISTENT.

Seems like it should be so easy…

Here’s an ugly workaround, but a workaround non-the-less:

Make a new table called “Timestamps”. The only field you need is a Created At field to time stamp each record.

In your main table, instead of Checkbox A, you have Linked Record A. To check A, you Add a new Linked record to that field with the + button and the “Add New Record” option. In field B, do a lookup on the Created At field from your Timestamps table.

It should do what you need, albeit in an ugly way…

Yeah, at that point it’s faster to just manually enter a timestamp. At this point we’d kill for even just the functionality of ⌘-; which we can use in Google Sheets.

Sorry for taking so long to get back!

I ended up using Keyboard Maestro for iOS to insert the date, I use it for other record updates so I was easy to code this to do it.

Regards

Paul

Know of a Mac OS equivalent?

Keyboard Maestro IS a Mac app.

Sorry I meant Mac OS

Thanks. I bought a copy of Keyboard Maestro. What’s you config for timestamp population?