Timecard for "shift" workers help needed?

[MrJive]

I'll admit to having myself thoroughly confused at this point!

Day Shift - 6:00 am to 02:00 pm
Evening Shift - 2:00 pm to 10:00 pm
Night Shift - 10:00 pm to 6:00 am

Given an employee's Start Time and Stop Time (for any given day) -
calculate number of hours worked for each shift and total hours worked

For extra credit (lol) - Weekday shifts begin at 6:00 am on Monday and
end at 10:00 pm on Friday. All other shifts are Weekend shifts for a
total of six different shifts.

I've been working at this for some time now, and have just been going
in cicrcles for the last several weeks!. It's time I asked for help and
gotten some more minds working on it.

Any help would be appreciated.

 

[Intruder9]

See if this is what you want
http://www.j-walk.com/ss/excel/files/timesht.htm

 

[MrJive]

Not quite. I have a "basic" timecard that can do the hours calulations,
time entry conversions, etc. But they don't handle the "shift
diffrential pay" issues.

Under our pay system, an employee is entitled to an additional hourly
compensation for all hours worked during the evening shift and/or the
night shift.

For example: an employee who works a 12 hour shift, arriving at 5:00 am
and leaving at 5:00 pm would be entitle to receive one (1) hour of
night pay (5:00 am to 6:00 am). eight (8) hours of day pay (6:00 am to
2:00 pm) and 3 hours of evening pay (2:00 pm to 5:00 pm).

There are other considerations (i.e., week-end, holiday, overtime,
etc.) but I can handle all of those if I can only come up with the way
to calulate how many hours in each shift an employee worked based on
the start and stop times for any given emplyee on any given day.

I've been able to do this with an unbelievable number of intermediate
steps, columns, formulas, etc., but remain convinced that there is
probably an elegant way to handle the calculations in a single (or at
least three simple formulas - one for each shift)

Can anyone help further?

 

[MrJive]

Not quite. I have a "basic" timecard that can do the hours calulations,
time entry conversions, etc. But they don't handle the "shift
diffrential pay" issues.

Under our pay system, an employee is entitled to an additional hourly
compensation for all hours worked during the evening shift and/or the
night shift.

For example: an employee who works a 12 hour shift, arriving at 5:00 am
and leaving at 5:00 pm would be entitle to receive one (1) hour of
night pay (5:00 am to 6:00 am). eight (8) hours of day pay (6:00 am to
2:00 pm) and 3 hours of evening pay (2:00 pm to 5:00 pm).

There are other considerations (i.e., week-end, holiday, overtime,
etc.) but I can handle all of those if I can only come up with the way
to calulate how many hours in each shift an employee worked based on
the start and stop times for any given emplyee on any given day.

I've been able to do this with an unbelievable number of intermediate
steps, columns, formulas, etc., but remain convinced that there is
probably an elegant way to handle the calculations in a single (or at
least three simple formulas - one for each shift)

Can anyone help further?