A script like this will give the net time on a task using the 'gap and island' principle (unfortunately all references seem to relate to SQL). See also the VBA version of this which the following is adapted from:
function myFunction(prange) {
var p = sheet.getRange(prange);
var array = p.getValues();
// Sort on start time
array.sort(function(a, b) {
return a[0] - b[0];
})
// Initialise
minStart = array[0][0];
maxEnd = array[0][0];
gap = 0
// Loop over rows
for (i = 0; i<array.length;i++)
{
// If there is a gap, increment total gap length
if (array[i][0] > maxEnd )
gap = gap + array[i][0].valueOf() - maxEnd;
// Update latest end time
if (array[i][1] > maxEnd)
maxEnd = array[i][1];
}
TimeOnTask = (maxEnd - minStart - gap)/(60*60*1000);
return TimeOnTask;
}
Here is the whole script with ability to pass an array (so filter can be used) and checking for non-dates added:
function myFunction(param) {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var r,array;
if(typeof param =="string")
{
r = sheet.getRange(param);
array = r.getValues();
}
else
array=param;
//Check for non-dates
filteredArray = array.filter(function(value) {
return Object.prototype.toString.call(value[0]) == "[object Date]" && Object.prototype.toString.call(value[1]) == "[object Date]";
});
// Sort the array
filteredArray.sort(function(a, b) {
return a[0] - b[0];
})
// Initialise
minStart = filteredArray[0][0];
maxEnd = filteredArray[0][0];
gap = 0
// Loop over rows
for (i = 0; i<filteredArray.length;i++)
{
// If there is a gap, increment total gap length
if (filteredArray[i][0] > maxEnd )
gap = gap + filteredArray[i][0].valueOf() - maxEnd;
// Update latest end time
if (filteredArray[i][1] > maxEnd)
maxEnd = filteredArray[i][1];
}
TimeOnTask = (maxEnd - minStart - gap)/(60*60*1000);
return TimeOnTask;
}
So you can pass it the filtered data like this:
=myfunction(filter(D2:E,B2:B=J1,C2:C=J2))
to give this:
