calculating time

[rjassal82]

Hi

I have a timesheet with different shifts workers have worked.

I have a time in and time out, lunch in and lunch out. I want to calculate the number of hours which fall between 8:00 till 18:30 and the number of hours which fall between 18:30 till 8:00. I need to minus the lunch in and out from this figure. I charge the client different rates for those 2 times so I just want to calculate the number of hours so that i can show my client the number of units that fell under those shifts.

Can someone help me please?

Thanks

[daddylonglegs]

Do some of your shifts pass midnight or are they all on one day?

[rjassal82]

They are all in one day, thanks.

[daddylonglegs]

I have a time in and time out, lunch in and lunch out.

let's assume those are in A2, B2, C2 and D2 respectively then you can use this formula in F2 to get the time worked between 08:00 and 18:30 (assuming lunch is always within those hours)

=MIN(B2,"18:30")-MAX(A2,"8:00")+C2-D2

then in G2 for the rest of the hours:

=B2-A2+C2-D2-F2

[rjassal82]

Thanks, how do I get it to enter a zero if no time was entered?

[daddylonglegs]

Try adding an IF function to the first formula like this:

=IF(A2="",0,MIN(B2,"18:30")-MAX(A2,"8:00")+C2-D2)

[newdoverman]

This is a timesheet that I have "played around" with for a while. It might have features that you can make use of.

The protected areas are protected without password.

[rjassal82]

thank you very much