Google Sheets – Timesheet Calculations

google sheets

I have a timesheet that I use to log my time.
It works fine if I log in and log out.
It works at the end of the day but doesn't work in all configurations. For example, I want it to work if I only arrive then leave at the end of the day but also show total of hours for the day if I am logging back in after a break.

My example shows the configurations that don't work.

EDIT: Updated Example sheet so anyone can use it. What I do is hide all but the current week. Then go to the HTML view and copy+paste that to my payroll person.

Best Answer

If you use this formula, then all circumstances will be taken into account:

=ARRAYFORMULA(
  IF(J2:J6<>"",J2:J6-C2:C6,
  IF(H2:H6<>"",H2:H6-C2:C6,
  IF(F2:F6<>"",F2:F6-C2:C6,
  IF(D2:D6<>"",D2:D6-C2:C6))))
    -ARRAYFORMULA(
      IF(E2:E6<>"",E2:E6-D2:D6)+
      IF(I2:I6<>"",I2:I6-H2:H6)+
      IF(G2:G6<>"",G2:G6-F2:F6)))

See your own file for the result.

Related Topic