Time Sheet Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a time sheet where all time worked between 6:30 am to
2:30 pm goes in one column, and time else where goes in another column. For
some reason my company tracks hours worked between 6:30 am and 2:30 pm.

Can anyone help?
 
I guess I didn't ask the question right. What I would like to do is have
staff sign in and out and then put the time worked between 6:30 am and 2:30
pm in one column and time elsewhere in another column.


Time In Time out morning elsewhere

6:00 am 3:00 pm 8 1
 
Try this:

A2 = Time in
B2 = Time out

Shift hours
H2 = 6:30 AM
I2 = 2:30 PM

D2 = formula for morning hours:

=IF(COUNT(A2:B2)<2,"",(IF(B$2<H2,0,IF(A$2>I2,0,IF(B$2>I2,I2,B$2)-IF(A$2<H2,H2,A$2)))/(B$2-A$2)*(B2-A2+(B2<A2)))*24)

E2 = formula for elsewhere hours:

=IF(D2="","",(B2-A2+(B2<A2))*24-D2)

Biff
 
That formula can be cleaned up a little bit since it is intended to only
work on less than a 24 hour period - 12:00 AM to 11:59 PM

=IF(COUNT(A2:B2)<2,"",(IF(B2<H2,0,IF(A2>I2,0,IF(B2>I2,I2,B2)-IF(A2<H2,H2,A2)))/(B2-A2)*(B2-A2))*24)

Biff
 
Back
Top