Counting hours in a day not associated to any records

[Brian_Pacelli]

I use Airtable to track my hours worked. I use two fields for Time Start and Time End. I am currently counting task hours with the formula:

(DATETIME_DIFF({Time End},{Time Start},‘minutes’)/60)

Sometimes I am working on multiple projects and, for various bookkeeping purposes, it’s appropriate to track both activities simultaneously.

For example, Project A will be a record with start time 14:00 and end time 16:00, while Project B will be a record with start time 15:00 and end time 17:00. There is a 1.0 hour overlap.

With my current method, the daily sum of hours would be 4.0. However, by actual hours worked is only 3. How can I count actual hours worked?

My idea is to count all the hours in the day which a record does not occupy, and then subtract that number from 24. How can I configure Airtable to do this?

Thanks

[kuovonne]

You cannot do this with formula fields. You can do this with scripting.

Counting all the hours which a record does not occupy is not any easier than counting hours which a record does occupy. Your technique may also need adjusting if begin/end times ever are not exactly on the hour.

The exact approach will depend on your base structure, but I would approach writing this script by finding overlapping time frames and collapsing them into one bigger time frame. Once all time frames have no overlaps, then adding up all the time frames.