Referencing cells above and to the left in script

I'm trying to make a script for a custom function in google spreadsheet to work out the hours worked in a day `function hwtd()`. I've got it perfect in the cell `(((F8-E8)+(F9-E9))+(IF((F10<E10), (F10-E10+\$Q\$3), (F10-E10))))` with the host cell being F11 through its long winded to type out and copy/paste breaks my flow of work.

My problem is after searching I can't work out how to reference cells relative to the host cell i.e. `[cell 3 above] - [cell 3 above and 1 left] = hours in shift 1 of 3` (then repeated again for shift 2 & 3 before adding them all together).

The math I can do, it's just the referencing from the host cell. What is the best way to resolve this and are you able to explain it as well?

-
Why don't you share a doc with us and show us the expected outcome? – Jacob Jan Tuinstra Mar 3 '14 at 12:52
here is a link to the doc containing the data docs.google.com/spreadsheets/d/… – user3373268 Mar 3 '14 at 20:40
From your question, it is not clear that your interested in a script. What have you tried yourself? – Jacob Jan Tuinstra Mar 6 '14 at 21:11
function hwtd() { // Function to calculate hours worked in the day... var outNum = 0; // this will hold the answer... outNum = [cell 3 above] - [cell 3 above and 1 left] = hours in shift 1 of 3 // this the part where i need to reference the cells but repeated for the whole 3 shifts... return outNum; // return the answer to the cell which has the formula... } – user3373268 Mar 6 '14 at 22:18
Do you still want a script or are you happy with the solution I provided? – Jacob Jan Tuinstra Mar 9 '14 at 21:14

What you basically need to do is the following:

sum the difference between the shift endings minus shift beginnings

That will result in this formula:

``````=SUM(ARRAYFORMULA(Q4:Q6-P4:P6))
``````

Example

I've prepare an example file for you, containing to styles: Working Hours. The formulae are easily copied/pasted to fill in the next worker.

Style 2

-
Thanks for that, it makes the whole function easier than what i was doing. I was originally looking for how to reference a cell from the host cell when using a google script so when i use the custom function in a cell it can refer to the cells relevant to where it is in the sheet – user3373268 Mar 5 '14 at 21:23
@user337368 You're welcome. Please mark the answer, if you will (webapps.stackexchange.com/help/accepted-answer) – Jacob Jan Tuinstra Mar 5 '14 at 21:27
@user3373268 Are you familiar with Google Apps Script? – Jacob Jan Tuinstra Mar 5 '14 at 21:28
a slight bit but not too much – user3373268 Mar 5 '14 at 22:02