+ Reply to Thread
Results 1 to 3 of 3

Complex shift differential calculation (as only a gov't could make this up)

  1. #1
    Registered User
    Join Date
    02-18-2014
    Location
    Baltimore, MD
    MS-Off Ver
    Excel 2010
    Posts
    2

    Post Complex shift differential calculation (as only a gov't could make this up)

    Hi All!

    This is my first post so lets see how it goes. I have tried searching many different forums for an answer close to what I need but haven't found one. Maybe someone here could help? I am helping my mom with creating an excel timesheet for the employees at her company (around 60). I currently have 3 spots per day to clock in/out (see attachment). I have summed the total hours per day and how many overtime hours per day. My next step is to calculate the shift differential per day. Here is what the company defines as shift differential:

    "If more than 50% of the shift is worked between 6pm and 6am then the ENTIRE shift gets shift differential pay. If less than (or equal to) 50% of the shift is between 6am and 6pm NONE of the shift gets shift differential pay."

    Yes, I know this is asinine. It would be much easier and more straightforward to do it the normal way. But since the rules will not be changed what is the best way to accomplish this? First, formula or macro? Next, what specific text should the best way contain? I have some decent experience with formulas but nothing with macros other than being able to tell you what a macro is.

    Thanks for all your input!!!!!!!!!!
    Attached Files Attached Files

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Complex shift differential calculation (as only a gov't could make this up)

    The formula in I9 would more accurately be

    =24 * SUMPRODUCT(C9:H9 * -1^{1,2,3,4,5,6})

    Regarding the SD, dos that mean that more than half of the hours actually worked must be outside 6 to 6? E.g., a shift from 5PM to 7:30PM, with a half-hour break at 6PM, would not qualify?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    02-18-2014
    Location
    Baltimore, MD
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Complex shift differential calculation (as only a gov't could make this up)

    Thanks for the better formula! Regarding your question; yes, more than 50% of the total hours worked for that day have to be between 6pm to 6am. So you can clock in and out multiple times but the majority of the hours worked have to be after 6pm and before 6am

    Thanks for any further insight!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Timesheet calculations with overtime and shift differential
    By JHSVic in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-17-2014, 10:57 AM
  2. Need help to calulate shift differential time.
    By chrisstiff1 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-18-2013, 09:29 AM
  3. identify differential hours in a shift that goes over midnight
    By CanYouDoThis? in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2012, 03:20 PM
  4. Function that can identify Shift Differential...
    By rsstites in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-14-2008, 06:44 PM
  5. shift differential
    By flyeaglesfly in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-14-2008, 04:09 PM

Tags for this Thread

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