3

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?

Improve this question
6
  • 1
    Why don't you share a doc with us and show us the expected outcome?
    – Jacob Jan Tuinstra
    Mar 3, 2014 at 12:52
  • here is a link to the doc containing the data docs.google.com/spreadsheets/d/…
    – user3373268
    Mar 3, 2014 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, 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... }
    – user3373268
    Mar 6, 2014 at 22:18
  • Do you still want a script or are you happy with the solution I provided?
    – Jacob Jan Tuinstra
    Mar 9, 2014 at 21:14

1 Answer 1

Reset to default
2

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 1

enter image description here

Style 2

enter image description here

Improve this answer
4
  • 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, 2014 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, 2014 at 21:27
  • @user3373268 Are you familiar with Google Apps Script?
    – Jacob Jan Tuinstra
    Mar 5, 2014 at 21:28
  • a slight bit but not too much
    – user3373268
    Mar 5, 2014 at 22:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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