Help

Re: Timestamp for when checkbox has been checked

5259 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Jacob_DuBois
4 - Data Explorer
4 - Data Explorer

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!

29 Replies 29
Andy_Lin
6 - Interface Innovator
6 - Interface Innovator

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.

David_Smedberg
6 - Interface Innovator
6 - Interface Innovator

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

Jerry_Chiang
5 - Automation Enthusiast
5 - Automation Enthusiast

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
4 - Data Explorer
4 - Data Explorer

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

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

Screenshot 2018-01-17 13.26.34.png

Rob_G
6 - Interface Innovator
6 - Interface Innovator

This works for me, very handy thanks.

Zach_Johnson
4 - Data Explorer
4 - Data Explorer

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.

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…