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.