Calculating peak and off-peak hours while filling out employee's schedules

[diggidydave]

This is a bit complicated. I will try to explain the best I can. I would simply like to know where to start looking to solve this problem.

I need a way of tracking several 'types' of hours including calculating peak and off peak hours when scheduling employee's time sheets. these are the criterias.

- off peak hours are between monday (9am) through wed (7pm)
- peak hours are wed (7pm onward) - sunday (end of day)
- if an employee works over 8 hours, it's only counted as 7.5 hours of pay (subtracting lunch/dinner)

i need a way of entering times into excel so that these hours will be calculated in some shape or form for any given month for several employees. this is for a sales company and therefore, individuals working more during peak times will have a higher quota and vice versa (to be worked out elsewhere on this sheet). when i first tried to tackle this, i ended up with a lot of extra columns that held values for each day per employee. this ended up very messy. are there some forumlas that can help keep track of this data and help minimize extra columns? hope this all makes some sense. thanks in advance!

[daddylonglegs]

If an employee works from 3:00 PM Wednesday to 11:30 PM Wednesday then that should only constitute 8 hours worked but how many hours are peak or off peak, i.e. where is the ½ hour break deemed to be?

How are you recording start and end times, it would be easier with time and date in one cell for start time of a shift and the same for the end of shift, or do you need to do it another way?