Overtime Calculations and Summary

[rwau]

Hello,

Wondering if anyone has some info that may help me out with my current issue. I have been given a report (sample attached) that contains start and end dates for when employees have worked overtime. My end result would include a summary by week (our week starts on Saturday and goes to Friday - I have NO idea why) of the total hours, per employee. The rules for calculating the hours are as follows:

• If the day is a holiday the number of hours is "hours worked" x 1.5
• If the date and time is between Saturday at 12 AM and Monday at 8:30 AM the hours are calculated as "hours worked" x 1.5
• For the remaining hours if the hours is 5 or less then "hours worked" x 1
• For the remaining hours if 6 or more then (("hours worked" - 5) * 1.5) + 5

In the attached spreadsheet an example of an ending result would be: For Emp Eleven the total hours for the week of November 9-15:

• November 11 is a holiday for us, so the total hours would be 2.5 x 1.5 = 3.75
• For the rest of the week it would be 5 hours (November 13) + 2 hours (2 of the 4 hours worked on November 15/16) = 7 hours, so ((7-5) x 1.5) + 5 = 8
• So the total would 11.75 hours for the week of November 9 to 15

Hopefully this makes sense...it's confusing me! I personally think this type of thing should be done by the manufacturer of the software but it would require customization ($$$) which is unfortunately not possible.

Thanks!!

[Keebellah]

Hi, I'll take a look and see if I can help, no promises so don't hold your breath

[rwau]

Thanks Keebellah (Hans)!! I honestly didn't know what to expect from this post, so any response at all is greatly appreciated.

Hi, I'll take a look and see if I can help, no promises so don't hold your breath

[Keebellah]

I'll try my best, just have to find the time to let it sink in and come up with a possible solution

[rwau]

Hello Keebellah! No need to hurt your brain on this one. If you do happen to come up with something I will definitely be appreciative. This little mini-project went from a "take your time" to a "I need it yesterday". Although it's not an ideal solution I used a series of formulas and basically force a very ugly solution! What I did was as follows, if it helps at all:

• On the data tab I created a formula to get the week number and flag to determine if the day was a weekend or holiday
• I then created tabs for Weekend and Holiday Summary, Weekday Summary and Travel Summary based on the Pay Code
• I then have a Total Summary tab
• Each of the tabs has a "manual pivot" type of table that lists all of the employees and all of the weeks of the year
• This formula calculates the total based on the rules: IF('Weekend and Stat Summary'!B3=0,IF('Weekday Summary'!B3>5,((('Weekday Summary'!B3-5)*1.5)+5),'Weekday Summary'!B3),IF(AND('Weekend and Stat Summary'!B3>0,'Weekend and Stat Summary'!B3<=5,'Weekday Summary'!B3<>0),('Weekday Summary'!B3-5+(2*'Weekend and Stat Summary'!B3))*1.5+5-'Weekend and Stat Summary'!B3,('Weekend and Stat Summary'!B3+'Weekday Summary'!B3)*1.5))+'Travel Summary'!B3 which I then copy to the rest of the cells

It's VERY ugly, but it gives the results that my colleague was looking for.

Thanks!

[Keebellah]

Sorry, but I was tide-up with a Excel project that needed completion.
Your solution sounds great, I was a little troubled with the week limits your company uses.

If you want to set it all to VBA, let me know

[rwau]

Apologies for the late reply on this one. I've been out of the office for a bit. No need for the VBA as it's not required due to an insane change in the requirements.

[Keebellah]

Okay, clear. You know where to find us
If this solves your post don't forget to mark it as solved