• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compute Regular hours and Over Time hours from Total Hours

Hello,
I am trying to compute regular hours and over time from total hours column. Please see attached sample spreadsheet with desired output.

Thanks for all your help
 

Attachments

  • Reg_Hrs & OT Hrs.xlsx
    10.8 KB · Views: 8
Hello,
I am trying to compute regular hours and over time from total hours column. Please see attached sample spreadsheet with desired output.

Thanks for all your help

Sorry, I believe I didnt give more info and I just realized that I made a mistake in my desired out. I am attaching file again. This file is the updated file

Regular hours for each employee should 40 hours for a week and anything over 40 hours are overtime hours. Sorry for the confusion.

Thanks for your help
 

Attachments

  • Reg_Hrs & OT Hrs.xlsx
    10.8 KB · Views: 5
Last edited:
excelnovice2000
Is Your used (Working) week from Monday to Sunday or how?
Based Your Regular hours for each employee should 40 hours for a week and anything over 40 hours are overtime hours. Sorry for the confusion.
and Your Desired Output ....
Why there are OT Hrs before 40 hours?
24-Oct ... 27-Oct is 32:30, 28-Oct + 8:30 >> 40:00 + 1:00 (OT)
Or
Do You use something like rolling week to solve OT?
... Mon to Sun 40hrs > Tue to Mon 40hrs > Wed to Tue 40hrs ...
 
excelnovice2000
Is Your used (Working) week from Monday to Sunday or how?
Based Your Regular hours for each employee should 40 hours for a week and anything over 40 hours are overtime hours. Sorry for the confusion.
and Your Desired Output ....
Why there are OT Hrs before 40 hours?
24-Oct ... 27-Oct is 32:30, 28-Oct + 8:30 >> 40:00 + 1:00 (OT)
Or
Do You use something like rolling week to solve OT?
... Mon to Sun 40hrs > Tue to Mon 40hrs > Wed to Tue 40hrs ...

Hello,
Work week is from Sunday to Saturday and If an employee works more than 8 hours in any given day, then the extra hours are considered over time.

Example: If an employee works 8.15 hours a day, then 8 hours is regular hours and 0.15 is over time.

If an employee works 55 hrs in 6 days ( Monday 9 hrs, Tuesday 10 hrs, Wedesday 10 hrs, Thursday 9 hrs, Friday 9 hrs, Saturday 8 hrs. In this case
Monday 8 Reg Hours 1 OT Hours
Tuesday 8 Reg Hours 2 OT Hours
Wednesday 8 Reg Hours 2 OT Hours
Thursday 8 Reg Hours 1 OT Hours
Friday 8 Reg Hours 1 OT Hours
Saturday 8 OT hours

So Reg Hours 40
OT hours 15


Sorry, still trying to understand US labor laws.

Thanks for help.
 
Last edited:
excelnovice2000
So why You've written too:
Regular hours for each employee should 40 hours for a week and anything over 40 hours are overtime hours. Sorry for the confusion.
There were nothing about daily hours.
That could be so different than Your other ... version.
 
excelnovice2000
So why You've written too:
Regular hours for each employee should 40 hours for a week and anything over 40 hours are overtime hours. Sorry for the confusion.
There were nothing about daily hours.
That could be so different than Your other ... version.

Sorry for the confusion again. Yes the regular hours and OT hours are calculated on a daily basis. Please ignore this sentence "Regular hours for each employee should 40 hours for a week and anything over 40 hours are overtime hours. Sorry for the confusion."

The below scenario is what I am trying to accomplish

If an employee works 55 hrs in 6 days ( Monday 9 hrs, Tuesday 10 hrs, Wedesday 10 hrs, Thursday 9 hrs, Friday 9 hrs, Saturday 8 hrs. In this case
Monday 8 Reg Hours 1 OT Hours
Tuesday 8 Reg Hours 2 OT Hours
Wednesday 8 Reg Hours 2 OT Hours
Thursday 8 Reg Hours 1 OT Hours
Friday 8 Reg Hours 1 OT Hours
Saturday 8 OT hours

So Reg Hours 40
OT hours 15


Thanks for input.
 
Can you explain why Saturdays' peculiar Regular/Overtime splits?:
81655

You've also got a Sunday (30th) in there, what are the REG/OT hours for Sunday?
 
Last edited:
excelnovice2000
One my way sample
Please, next time verify Your expected results as well as rules.
Usage:
#1 Enter Your data as much as You would like to calculate
#2 Press [ Do It ]
 

Attachments

  • Reg_Hrs & OT Hrs.xlsb
    19.1 KB · Views: 3
Back
Top