I do apologize for not replying sooner. This may work for you and please let me know if this does not. I would use this formula for Cell E4 then copy & past that cell to all the others in row column E below row 4. It will not work on E3, because there is no data it could go by on the previous row.
Code:
=IF(C4=B4,IF(C3=0,1,2-C3),IF(AND(C3>=0,C3<B4),B4-C3,(1-C3)+B4))
Explanation of the code:
Below: IF C4
(clock out time) = B4
(clock in time) Meaning the person did not work at all that day. Then IF C3
(previous clock out time0 = 0
(Midnight) OUTPUT 1
(Integer for 24 hours in excel) ELSE 2
(Integer for 48 hours in excel) –C3
(previous clock out time).
Code:
=IF(C4=B4,IF(C3=0,1,2-C3)
IF
above C4 does not equal B4 then this next formula will run.
Below: IF C3
(previous clock out time) is greater than or equal to 0
Midnight and before the next clock in time on the same day THEN
OUTPUT B4
todays clock in time – C3
previous clock out time ELSE
OUTPUT 1
(Integer for 24 hours in excel) – C3
(previous clock out time) + B4
(current clock in time)
Code:
,IF(AND(C3>=0,C3<B4),B4-C3,(1-C3)+B4))
TIP: I had to change the rest hours format to 00:00:00 to display anything above 24 hours correctly. Also for some reason Excel is programed to know what you mean when you put the 24-C3+B4 instead of giving you some ridiculously large number like it did when I tried 48 hours with the number 48. After finding
How to Use Excel's HOUR Function | eHow.com I found out what I was doing wrong.
Please let me know if this needs any more explaining or it does not work for what you need.
- William
Edited: at 3:25PM (EST) for correction on the formula.