Multi-Record Date/Time Calculations

[CGK]

Hello,

I am trying to create a field where I can calculate the difference in time from one record to another. I.e. calculating the time difference from Time Left Work (from the previous record) to when the commence work (the current record).

I have read various multi-record calculations but I am unsure how best to approach this. Any assistance would be appreciated.

Thanks!

[W_Vann_Hall]

Well, you’ve taken the proper first step: As far as I know, the only way to perform record-to-record calculations is through the somewhat complicated process described here. (Actually, let me take that back: It can also be done using such a 3rd party SaaS integration service as Zapier or Integromat.)

However, you’re in luck: Your application is closer to the one outlined in my responses to this post. (Make sure you scroll down to the improved version described in the reply dated March 25th.)

Here’s a variation on the gold price demo, adapted for your use.

The only atypical aspect is that I use the 'X' format specifier for DATETIME_FORMAT() to convert both {Time Left Work} and {Work Commences} to the Unix timestamp (the number of seconds since 1 January 1970). When I subtract the prior day’s {Time Left Work} from today’s {Work Commences}, the remainder is the number of seconds between the two timestamps. Since Airtable’s duration field is stored in seconds, I simply formatted the remainder as a duration to get the number of hours and minutes between the two times.