I run the clock-in, clock-out system at my organization and currently we use a custom function to determine the total times people work based on their punches.
For example, if a user's entries for a day are: 11 AM - 12PM, 1 PM-2 PM
The custom function I've written will output : 2
However, we have run into some problems using the custom function (such as code doesn't always copy between Google Sheets when making copies, and custom functions just sometimes randomly stop working). These can usually be resolved with human intervention, but that costs time and money and we have 1500+ part time staff getting their time approved across 20+ sheets that want to get paid on time.
Thus, I've been working on a non-custom function to sum the sets of times in a single cell.
Summing one set of times is fairly trivial:
11 AM - 12 PM
Using the formula minus(index(split($A2,"-"),1,2),index(split($A2,"-"),1,1))*24
We get 1.
However, I cannot for the life of me figure out how to get multiple sets of times to sum without explicitly typing the function multiple times. My intuition is that the solution will use ARRAYFORMULA, but thus far I can only get it to sum the first set of times:
11AM - 12PM, 1 PM - 2 PM
Formula: arrayformula(sum(minus(index(split(split($A5,","),"-"),1,2),index(split(split($A5,","),"-"),1,1))*24))
Result: 1
We cannot split the times into multiple cells as an intermediate step because of the way the spreadsheet is set up. Even if we could, an employee could theoretically have an infinite number of sets of times (something like 5 would be a realistic theoretical cap, but still... we don't want to have 4 extra columns that are almost never used).
Here is a view-only Google Sheet with some examples. Feel free to make a copy and play with your copy as much as you like.
https://docs.google.com/spreadsheets/d/1gVZ09OT_PMSCWocIYW9FM9QfEmLVUI1-LWu1b8HsOsQ/
This works in this editable copy...