Google-sheets – Referencing cells above and to the left in script

google sheetsgoogle-apps-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?

Best Answer

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

Related Topic