Skip to main content

Calculating the difference between time/date values between records

  • June 18, 2019
  • 4 replies
  • 41 views
C

I am using this table as a punch clock. Each record is a time stamp for moving to a different repair order or stopping work entirely. So Each record needs to be able to identify how long it was until another record was created (only within a specified view). I currently have integromat free version hooked up for another table, so if I can avoid 3rd party integration for this that would be great.

Also noted: The primary field obviously uses the created time field to time stamp, but I also have a date/time field worked in with an IF statement that can be used to effectively “edit” the created time.

Thank you.

4 replies

A
Forum|alt.badge.img+19
  • AlliAlosa
  • Inspiring
  • June 18, 2019

Hi there,

There might be an easier way… but I think you can accomplish this with a self-linking table. Create new records by linking them to the previous one.

You can then use the DATETIME_DIFF() function to get the difference in time. Example below uses minutes.

IF({Next Record}, DATETIME_DIFF(DATETIME_PARSE({Next Record}, 'M/D/YYYY h:mm a'), Name, 'minutes') & " Minutes")

Justin_Barrett
Justin_Barrett
Forum|alt.badge.img+21

To add to the solution by @AlliAlosa, I suggest creating a view with a filter to only show records where {Next Record} has no link, switching on that field’s “Limit record selection to a view” option, and targeting that view. That will make it a lot easier to add new links, because only the latest two unlinked records will appear (the one you’re creating a link in, and the target of that link), instead of all records in the whole table.

A
A
Forum|alt.badge.img+19
  • AlliAlosa
  • Inspiring
  • June 18, 2019

To add to the solution by @AlliAlosa, I suggest creating a view with a filter to only show records where {Next Record} has no link, switching on that field’s “Limit record selection to a view” option, and targeting that view. That will make it a lot easier to add new links, because only the latest two unlinked records will appear (the one you’re creating a link in, and the target of that link), instead of all records in the whole table.


This is a great idea. I implement something similar whenever I’m training someone who doesn’t understand Airtable well. Great way to force them to pick the correct record.

C
  • Curtis_Budka
  • Author
  • New Participant
  • July 22, 2019

Thank you, I think I will be able to plug this in to integromat to automate.

Terms & ConditionsAccessibility statement