Skip to main content

Formula for Subtracting Link Times - (Not Duration Times)?

  • January 11, 2019
  • 1 reply
  • 42 views
K

Hi,

I have creating a time sheet with the following:

  • Start times in a table at increments of 15 minutes - 6am, 6:15am, 6:30am etc…
  • End times in a table at increments of 15

I have created a form which links to the start and end time tables. I then have a formula which is meant to subtract the input data.

My problem is that Airtable is unable to calculated the difference of the end time 17:00 (or 5pm) to the start time 7.00am.

The formula works if the field is done as duration. But from the guys who send the forms, they struggle to enter direct inputs as hh:mm. They prefer to have a selection of time in increments rather than putting a time input.

Any help would be much appreciated!

Regards,
Kevin

1 reply

W
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • January 11, 2019

How are you assigning start and end times? Are they being chosen from a single select or from a linked record? (If the latter, I’d recommend re-implementing with a single-select, as you’re invoking a lot of unnecessary overhead — unless, of course, you are in some way making use of the link between records.)

In either case, the problem is your times are not date values but are either text strings or arrays, instead. Airtable will properly calculate the difference between the two times as long as you convert the timestamps to datetime fields first. To do so you use the DATETIME_PARSE() function with the appropriate format specifier. For instance, if you are using a 24-hour clock on both start and end times, the following will give you the elapsed time in minutes:

DATETIME_DIFF(
    DATETIME_PARSE({End Time},'HH:mm'),
    DATETIME_PARSE({Start Time},'HH:mm'),
    'm'
    )

(This assumes a consistent 4-digit timestamp — e.g., 06:00, 07:15. If your start times do not have a leading zero, use a format specifier of 'H:mm'.)

If you are using a 12-hour clock, you’ll want to use this formula:

DATETIME_DIFF(
    DATETIME_PARSE({End Time}&' am','hh:mm a'),
    DATETIME_PARSE({Start Time}&' pm','hh:mm a'),
    'm'
    )

To return elapsed time in hours, rather than minutes, use 'h' in place of 'm' as the unit specifier in DATETIME_DIFF().

If you are setting start and end times using linked records, retrieving them via a lookup, the values are being returned as single-element arrays. You can process them using the same formulas shown above, except in the 24-hour version, the two DATETIME_PARSE() functions should read:

DATETIME_PARSE({End Time}&'','HH:mm'),
DATETIME_PARSE({Start Time}&'','HH:mm')

This will cast the arrays as strings, which can then be processed by DATETIME_PARSE().

E
Terms & ConditionsAccessibility statement