Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  lazfish Jun 7 '13 at 16:35
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
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. –  lazfish Jun 4 '13 at 4:32
    
And good catch on the fix! –  lazfish Jun 7 '13 at 16:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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