TIME SHEET FORMULA CONDITIONS" ARE THEY POSSIBLE"

[bx lifeguard]

I created a time sheet. The time card information is inputted into the time
sheet to calculated with hours worked and rate. This is great until an
employee is late or leaves early. Excel will calculate the late time and end
time whether employee punched out on time or earlier than the hour. My
question is this.

Ex: Employees hours 9 am - 5 pm without lunch worked in.
Employee punched in late 9:02 (start time should be 9:15 am)
Employee punched out earlier than 5 pm punched out at 4:49 pm,
(end time should show 4:45 pm). This is the actual time in hours
worked.

Excel will calculate from 9:02 unitl 4:45 pm. This is actually wrong
because employee should be docked a total of an 1/2 hour. Can this formula
be created to calculate from quarter to quarter no in between. If punched
earlier can it also be created to go back to the quarter previous if punched
after the quarter when puching out.

please help!

 

[JE McGimpsey]

XL stores times in fractional days, so there are 96 quarter hours per
day. So one way:

A1: <start time>
B1: <end time>
C1: =MOD(FLOOR(B1,1/96)-CEILING(A1,1/96),1)

the MOD() is to allow shifts to span midnight.