Timestamp for when checkbox has been checked

[Jacob_DuBois]

I’m working on a trouble ticket table that keeps track of support for our users. I want to be able to track when a box was checked. So I have a “Followed up” checkbox field and I’d like to track the time from when the record was created to when the box is checked. I’m pretty sure I know how to figure out the difference in time with a formula but I’m having a hard time getting the timestamp of the box being checked. I can see the time in the expanded history view but there’s no way to reference it as far as I can see.

Any help or workarounds are appreciated!

[Andy_Lin]

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:

Most Recent Date from another table Ask the community

Here’s how I would go about constructing a “Date of last contact” field for a table of clients linked to a table of client interactions. In the table of client interactions, I have a linked record field for the name of the client, a date field for the date of each interaction, and a primary field which uses a formula to name each record. [image] In the client table, create a rollup field which uses the linked record field connected to the client interactions table. Select the dates field (co…

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.

[Julian_Kirkness]

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.

[David_Smedberg]

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.

[Jerry_Chiang]

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’),"")

[Jerry_Chiang]

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.

[edhead76]

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

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

[Rob_G]

This works for me, very handy thanks.

[Zach_Johnson]

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.

[Andrew_Enright]

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…