# 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? Mar 3, 2014 at 12:52
• here is a link to the doc containing the data docs.google.com/spreadsheets/d/… Mar 3, 2014 at 20:40
• From your question, it is not clear that your interested in a script. What have you tried yourself? Mar 6, 2014 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... } Mar 6, 2014 at 22:18
• Do you still want a script or are you happy with the solution I provided? Mar 9, 2014 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 Mar 5, 2014 at 21:23
• @user337368 You're welcome. Please mark the answer, if you will (webapps.stackexchange.com/help/accepted-answer) Mar 5, 2014 at 21:27
• @user3373268 Are you familiar with Google Apps Script? Mar 5, 2014 at 21:28
• a slight bit but not too much Mar 5, 2014 at 22:02