Shift time & penalty rate formulas

Hi,

I'm trying to automate our payroll to calculate the number of hours worked to factor in shift times & penalty rates. At present, I have worked out how to calculate the number of hours worked with penalties but I don't know how get excel to recognise shift times.

I'm using the following formulas to work out;
1. Total hours worked (less unpaid breaks): =(D4-C4+(D4<C4))*24-0.9
2. Standard hours: =IF(E4>=7.6,7.6,E4). This is for a normal full working day (7.6hrs)
3. Time & a half penalty rate (first 2 hours): =IF(E4>=9.6,2,E4-F4)
4. Double time penalty rate (each hour thereafter the first 2 hours): =IF(E4>=15,0,E4-F4-L4)

These formulas work well but it won't work when I try & add shift time parameters. The shift times are worked out on the following time periods:
1. Morning shift: Shifts that finish between 12:30 & 14:30
2. Early afternoon shift: Shifts that finish between 19:00 & 21:00
3. Afternoon shift: Shifts that finish between 21:00 & 23:00
4. Night shift: Shifts that finish between 23:00 & 07:00

These shifts attract penalty rates also so I need a way to allocate the time to the correct cell. The actual $$ value calculations are done elswhere on the sheet.

I've tried a number of different ways to overcome it but I don't have a lot of experience with excel & only know how to use basic formulas. Any assistance would be greatly appreciated>

Cheers,
James

Hi Dave,

Thanks for your quick reply.

I have attached a copy of the sheet I'm working from for you to look at. I was thinking that a 'between' or other command could be added to solve the issue>

Let me know what you think.

Cheers,

James

Er, 5 years on im looking for the final answer to this please!!