Hello i am trying to create a formula to calculate overtime in excel, the problem i am having is i need to calculate: total time, Regular Time and Overtime, but the overtime is only after 40 Regular time hours have been reached. i have it to where it will calculate overtime but it does it for any hours worked after 8. i just need after 40. i have a copy of the time sheet attached. thank you!!! need this ASAP!!! ALSO i need to keep the formula in total time to calculate from one day into the next, we work 24/7 365 so we can go from one day into the next on the same shift.
Time sheet in Excel
We will be implementing some important changes during 25th and 26th May 2024 which may result in an outage period of the website. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.




Re: Time sheet in Excel
Hello again,
Found in my archives an old Time Sheet example which might help you out ...:wink:
Hope this will help

Re: Time sheet in Excel
i need to calculate the overtime column to add the hours after they reach 40 from the regular time column.

Re: Time sheet in Excel
saY the person has worked
Monday 7:0023:00 16 hours
Tuesday 23:007:00 8 hours (rolled into another day)
Wednesday 15:007:00 16 hours (rolled into another day
Thursday 7:0015:00 8 hours
i need it to calculate the total hours for each day worked, regular hours for each day (8) and not add the overtime until the regular has reached 40 

Re: Time sheet in Excel
Did you take a look at the sample file : Test Overtime ...?

Re: Time sheet in Excel
I did and it works but it takes the 16 hours and splits them, 8 in Reg and 8 in OT. i need it to keep the 16 in reg until reg reaches 40 then have it go into the OT

Re: Time sheet in Excel
i posted a spreadsheet of what i need it to look like

Re: Time sheet in Excel
Attached is your workbook with a couple of formulas ...
Hope this will help

Re: Time sheet in Excel
i cant get the OT to work, i'm going to attached a copy of our actual time sheet so you can see what i ma trying to do. and thank you so much for helping with this


Re: Time sheet in Excel
Hello again,
Attached is your revised workbook ...
Are we heading in the right direction ...?

Re: Time sheet in Excel
After a couple of tests for cumulative weekly hours in cell O2 ... have adjusted the two basic formulas ...
Hope this will help
:smile:

Re: Time sheet in Excel
you are AMAZING!!!! thank you so very much!! its perfect

Re: Time sheet in Excel
Quote from tasha2515;797413you are AMAZING!!!! thank you so very much!! its perfect
Glad you could fix your problem ... :wink:
Thanks a lot ... for your Thanks ...:smile:

Re: Time sheet in Excel
@ tasha2515
What happens if the employee takes a 3 hour lunch?
Based on the formula you have in your attached 'Copy of Express Time Sheet new 1.0 Master.xlsx' at another forum, regular time excludes the lunch break.
The somewhat similar solution I posted 3 days ago at that forum took this into consideration.Had you cross posted in the appropriate manner, as advised on at least 3 forums, I wouldn't have wasted my time.
Anyway, would you please now have the courtesy to go to all your cross posts and inform you now have a solution and give credit where credit is due.
Thanks.


Re: Time sheet in Excel
Would you mind indicating the links to all the cross posts ...
Thanks

Re: Time sheet in Excel
Only the OP knows all the forums she posted the same request to.
There is at least these 3 others
https://www.mrexcel.com/forum/excelqu…html?highlight=
https://www.excelguru.ca/forums/showthread.php?8157overtime
http://www.msofficeforums.com/excel/36341ov…r40hours.html
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!