# Trouble calculating Overtime for Mutiple Rates and Employees [Many Conditions]

1. ## Trouble calculating Overtime for Mutiple Rates and Employees [Many Conditions]

Hi, I need some help and advice here on the worksheet attached.
I am trying to do 1 spreedsheet/template which I am able to use to fulfill different conditions on overtime as follows:

(1) Employee A
•Standard working hours are 8:00am - 5:00pm
•Monthly Salary
•Overtime : 5pm onwards @ 1.5 Rates

(2) Employee B
•Standard working hours are 8:00am - 5:00pm
•Daily Salary
•Overtime : 5pm onwards @ 1.5 Rates

(3) Employee C
•Standard working hours 8:00am - 5:00pm
•Hourly Rated
•Overtime : 5pm onwards @ 1.5 Rates

(4) Employee D
•Standard working hours 8:00am - 5:00pm
•Weekly Salary
•Overtime (1): 5pm - 12:00AM (midnight) @ 1.5 Rates
•Overtime (2): 12:00am (Midnight) - 800am @ 2.0 Rates

I managed to fulfill 1 - 3, however I am struggling to have the cells meet condition (4) to calucate the rates @ 2.0 after midnight. I hope the attached worksheet can explain what I am trying to do. Any advice or assistance is greatly appreciated. Thank you.

2. ## Re: Trouble calculating Overtime for Mutiple Rates and Employees [Many Conditions]

question..In sample, row 12 OT rate at 2 would be for 1/2 hour, or for the whole 2.5 hours ? In other words, is it just the part after midnight, or the whole part shift that occurs through midnight?

3. ## Re: Trouble calculating Overtime for Mutiple Rates and Employees [Many Conditions]

Hello it will be for 1/2 hour that occurs after midnight. Hence, OT rate @ 2.0 will be calcuated from 12 midnight to 8am.

4. ## Re: Trouble calculating Overtime for Mutiple Rates and Employees [Many Conditions]

Okay, I have also adjusted Column J a bit, just to subtract any OT2 hours( shows at the end of J11 as
-IF(ISNUMBER(K11),K11,0),"") the red part

so...try this :Workers Timesheet-Example.xlsx

Note- I left the zero values in OT 2 to make the formula easier to deal with, I suggest Conditional Formatting if you want them not to display

Hope this helps

5. ## Re: Trouble calculating Overtime for Mutiple Rates and Employees [Many Conditions]

Hi dredwolf,
Thank you so much! This certainly works and helps a lot

6. ## Re: Trouble calculating Overtime for Mutiple Rates and Employees [Many Conditions]

You are welcome

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