Short answer
From the question:
edit: where trying to use an implementation of the script where the result satisfies a condition, I'm not certain but I think the sheet "views" the formula input as giving a result, albeit blank? So not a value of "0"
Instead of something like
values[i][0] > 0
you should use
values[i][0] != ''
Explanation
Part 1: Misconception about blank cells and empty strings.
Your formula doesn't return a blank, it returns a empty string ""
. A blank cell is coerced to 0 always but a empty string doesn't.
Example:
If A1 has the following formula =""
, =ISNUMBER(A1) returns FALSE but =A1+1 returns 1.
By the other hand, on Google Apps Script a blank cell value is handled as an empty string, not as 0 (zero).
Google Apps Script is based on JavaScript no on the "spreadsheet thinking" so there are several things like the default value of a blank cell that are handle in a different way than it's on Google Sheets.
Part 2: One practice for Google Apps Script troubleshooting
In case of doubts, a general practice is to create simple test functions that return the result to the log but in the case of Google Sheets you could use a custom function if you feel more comfortable with the Google Sheets UI than with the Script Editor.
To check the value that Google Apps Script got from an empty string you could use something like the following custom function:
/**
* Returns a 2D array
*
* @param {"A1"} input Cell reference string
* @customfunction
*/
function valueDataType(input){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = ss.getRange(input);
var value = range.getValue();
var output = [
['Value: ' + value],
['Data type: ' + typeof value]
];
return output;
}
In all spreadsheet programs time is stored internally as a fraction of a day. Dates are stored as floating point number counting up from a specific day.
In your case you have split the dates and times into different cells. This means that when you subtract Column D from column B the program doesn't know about the date so it dutifully says that 11:31 AM comes before 10:21 PM and gives you a negative result. Because you are formatting the result as a number you see it as negative.
To get the value you expect you want to use the following formula to recombine the date and the time before doing the subtraction:
=C1+D1-(A1+B1)
If you format this as a number is will show you:
0.5486111111
To get in hours multiply it by 24 to get 13.16666667
Best Answer
The following formula will do just that:
It says: if the result of D1-C1 equals 0, then show nothing (""), else show the result.