On Tuesday 03 October 2006 05:20 am, | dMK | dINZO wrote:
> Hey there
>
> Just one more question with formulaes.......
>
> I'm not very good with words so I'm going to state what I'm trying
> to do.
>
> I'm trying to calulate employee's hours which with help from
> earlier have solved the problem. The thing is.... I need to deduct
> their breaks from the hours worked.
>
> I have tried using an IF funcation but to no avial. Could you tell
> me which function to use for this following example and if possible
> could you fully type out the equation?
>
> Any shifts that IS UNDER 6 hours will remain the same.
>
> Any shifts that IS OVER 6 hours and ABOVE will need to be deducted
> 30mins from their total hours.
>
> so Jenny who worked for 6 hours will have her break deducted hence
> her actual working hours was 5:30 hours
>
> Kind regards
>
> Johans
Try this:
Label the Columns:
A1: Names
B1: Clock in time
C1: Clock out time
D1: Time worked
Format the cells in Columns B through D as Time and 13:37. (This
means afternoon times will all have 4 digits (12:00, 13:00 [1:00 PM],
etc.)
Enter this formula in D2:
=IF(C2-B2<0.25;C2-B2;C2-B2-0.0208333333333333) and <Enter>. Select D2
again and drag the handle on the bottom right corner down as far as
the last name.
Explanation: These calculations are based upon parts of a day
with 24 hours = 1.00000000000000.
C2-B2<.25: check to see if this difference is less than 0.25 of a day
(that is the same as 6 hours).
If this difference is less than 0.25 of a day (6 hours), then enter
the difference.
If this difference not less than 0.25 of a day (6 hours), then
subtract 0.0208333333333333 from the difference. In this case, 30
minutes is this decimal fraction of a day.
Dan
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
