# shift differential

1. ## shift differential

I am having trouble figuring out a formula for shift differential and I hope someone can help.

My shift diff. are base on the start of the shift. If I start at or after 2000 or before 0559 I will be paid non-premium rate. If I start at or after 1200 or before 1959 I will be paid a premium rate. These rates are for 8 hours or less of hours worked in one day.

My hours are start time E3 and end time F3
My premium shift start time is I3 and end time is J3
My non-premium start time is M3 and end time is N3

2. What happens if the shift starts between 06:00 and 12:00, is that a standard rate? Assuming that your standard rate is in G3, premium rate in K3 and non-premium in O3 you could try this formula

=IF(E3>=M3,O3,IF(E3>=I3,K3,IF(E3>N3,G3,O3)))*MOD( F3-E3,1)*24

although it would probably be easier if you could set up a small table like this in A1:B5

from rate
00:00 £10
08:00 £12
12:00 £15
20:00 £10

and then use

=LOOKUP(E3,A2:B5)*MOD(F3-E3,1)*24

3. Thank you but I don't think that is the formula I'm looking for. I'll try to explain it better so you or someone else can understand.

Here are my 3 shift:
Standard shift 0600-1400

For the hours to be premium hours the shift must start at or after 1200 and before 1959 hour and only up to 8 hours.
For the hours to be non-premium hours the shift must start at or after 2000 and before 0559 hour and only up to 8 hours.

On my timesheet I have the hours that I worked as E3 (start time) and F3 (end time). Then I have I3 as my premium start time and J3 as my premium end time. I have M3 as my non-premium start time and N3 as my non-premium end time.

If possible here is what I'd like to happen. Example: If I put that I worked from 1400-2200 in E3-F3. I would like it to also show up in I3-J3 under premium shift. Example: If I put that I work from 1800-0200 in E3-F3. I would like it to show up in I3-J3 under premium shift.

Thank you to anyone who can help.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1