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

1. ## 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.

2. ## 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?

3. ## 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!

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