Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


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. – getthemike Jun 7 '13 at 16:35
up vote 1 down vote accepted

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


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. – getthemike Jun 4 '13 at 4:32
And good catch on the fix! – getthemike Jun 7 '13 at 16:33

Your Answer


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.