2

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.

  • The Wednesday registration is not correct. After lunch login, there's no depart - out. – Jacob Jan Tuinstra Jun 3 '13 at 21:30
  • Did it stop working? – Jacob Jan Tuinstra Jun 7 '13 at 13:36
  • Added brackets in the last statement, so that each if is independent. See your example file. Arrayformula's work throughout the whole column. So, basically you don't need to add more arrayformula's. – Jacob Jan Tuinstra Jun 7 '13 at 13:57
  • Nailed it! I am going to update my example when we are done and make it a blank sheet anyone can steal for their own abuse. What it currently doesn't show is the calculation breaking down each bi-weekly pay period. – gooddadmike Jun 7 '13 at 16:35
1

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.

  • Fantastic solution. Array Formulas are so powerful. Added a little conditional formatting so both text and background are white if is equal to 0 and this is perfect. – gooddadmike Jun 4 '13 at 4:32
  • And good catch on the fix! – gooddadmike Jun 7 '13 at 16:33

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.