+ Reply to Thread
Results 1 to 3 of 3

shift differential

  1. #1
    Registered User
    Join Date
    02-29-2008
    Posts
    15

    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
    Last edited by flyeaglesfly; 03-14-2008 at 07:30 AM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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. #3
    Registered User
    Join Date
    02-29-2008
    Posts
    15
    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
    premium shift 1400-2200
    non-premium shift 2200-0600

    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.
    Last edited by flyeaglesfly; 03-14-2008 at 09:41 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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