Create time sheet to automatically calculate hrs worked

I am trying to create a time sheet for my company that automatically calculates hrs worked.

So far I have where you can log a time in and a time out and it calculate your hours.
I also have it to where you can log a lunch out and a lunch in and it subtract the correct amount for total hrs that day.
I have it to where if you dont log a time in or time out it will return a blank.

This is where I am having problems, I want it to return a blank if there is a lunch time out but not a lunch time in OR return a blank if there is a lunch time in but not a lunch time out.

Here is my current formula:

=IF(OR(ISBLANK(I10),ISBLANK(L10)),"",(IF(OR(ISBLANK(J10),ISBLANK(K10)),(((L10-I10)-INT(L10-I10))*24),(((J10-I10)+(L10-K10))-INT((J10-I10)+(L10-K10)))*24)))

Here is a screenshot:
Excel Timesheet ScreenShot.jpg

Originally Posted by AliGW

Try changing the opening section to this:

IF(OR(COUNT(I10:L10)<2,COUNT(J10:K10)<2), ...)

Thank you that worked but now if there wasnt a lunch taken (lunch out and in both blank) then it returns a blank. I still need it to calculate the time in and time out if there wasnt a lunch taken.

Thank you very much!

Josh

Try this ...

=(L9-I9-IF(COUNT(J9:K9)=2,K9-J9,0))*24

Change 0 to the number you want

Originally Posted by AliGW

In your file, what are your expected results for the third line (not possible?) and the fourth line, please?

To return a blank in Q11
To return a blank in Q12

Originally Posted by AliGW

Give this a try:

=IF(OR(J9="",K9=""),((L9-I9)-INT(L9-I9))*24,(((J9-I9)+(L9-K9))-INT((J9-I9)+(L9-K9)))*24)

Originally Posted by Phuocam

Try this ...

=(L9-I9-IF(COUNT(J9:K9)=2,K9-J9,0))*24

Change 0 to the number you want

I couldnt get either of these to work

Thank very much for help me with this!

Originally Posted by AliGW

Mine works in your workbook ... So what exactly do you mean?

So when I use that formula when I put a time in it displays a 0 in the total for that day. I want it to display blank ("") until there is a time out also.

Also I want it to display a blank ("") if there is a time in, lunch out, a blank cell, time out OR time in, blank cell, lunch in, time out.

This is confusing but idk how to explain it better, tonight when I go home I can label a mock up of my results in red if that would help?

Josh

Haha! :

=IF(COUNT(J9:K9)=1,"",(L9+J9-K9-I9)*24)

Originally Posted by Phuocam

Haha! :

=IF(COUNT(J9:K9)=1,"",(L9+J9-K9-I9)*24)

Thank you very much!! This is almost excactly what I am looking for. Is there anyway to make it not display a zero when everything is blank and is there away it wont show a negative number when I input the first time?

Josh

Originally Posted by AliGW

Try this:

=IF(COUNT(J9:K9)<=1,"",if((L9+J9-K9-I9)*24<=0,"",(L9+J9-K9-I9)*24))

Worked perfect , thank you so much for all the help!!

Josh