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?

  • 1
    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

1 Answer 1


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:



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 1

enter image description here

Style 2

enter image description here

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.