hey everyone im new here and need some help with an excel formula please. Below is what i am trying to do i have also attached the file.
Cell G22 - Adds all Regular Working Hours Together
Cell H22 - Grabs Hours Over 8 Hours As Overtime
(This is correct)
Cell B22 - I would like this cell to add the Regular Work Hours and Overtime and subtract it from the weekly hours of 40 and display the remainder....basically i want this to show what goes over 40 hrs
Thanks
Salary Slip-Cashier.xlsx
Are Sat & Sunday hours overtime ??
If they work 2 hrs extra do they automatically get paid 4hrs ?
Do they work 8 and get paid for 8 or are the hours 8 but they get paid for 7.5 ?
Last edited by BlindAlley; 07-20-2015 at 10:46 PM.
Any day can be considered a overtime, because there is a DAY OFF. This day off changes, which means the set working days are 5 days (40/8 Hrs) + 1 Overtime (Hourly Rate) + 1 Day Off.
What ever goes beyond 40 Hrs or those 5 days is considered overtime.
If 2 extra hours is worked (42 Hrs Total) they get paid for 40 hrs (Set Salary) + 2 Hrs(Overtime).
If they work for example 9am - 5pm (This is 8 Hrs)..They get paid for 8 hrs.
Thanks
IN B22
=MAX(0,SUMPRODUCT(D15:D21-C15:C21)- "40:00")
formatted as [h]:mm
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
If you need hours as whole numbers to calculate pay, just format as general and multiply the entire thing by 24.
Based on your description, the daily hours do not directly affect overtime so your values in G22 and H22 could be incorrect. For example, if the person works 10 hour days for 4 days and then works a 7 hour day. G22 will read 39:00 and H22 reads 8:00 instead of 40:00 and 7:00. Is that correct? My formula gives you 7:00 in B22
With your formula for B22 it displays only 0:00. Doesn't work
Your correct ChemistB it is incorrect in G22 & H22, seems like it removes an entire hour instead of adding it to the overtime.
I have the Clock In & Clock Out formatted as Time Type 13:30
How it is formatted shouldn't matter (unless it's text)
See attached example
With your formula, if i remove "- "40:00" it seems to work
Is it because of my version of excel? Seems to work on my 2011 edition on the mac but doesn't on 2010 on windows.
Do you want it to estimate what the overtime will be at the end of the week? In your example, you only had 3 days filled in so far so if the person continued at that rate, they'd have 10 hours of overtime but they still only had 30 hours so no overtime.
I am working with 2010 on windows. Check to make sure your clock hours are numbers and not text.
I was looking at G15 & H16, and seems like its possibly off by a hour? Shouldn't C15 & D15 be basically 11 hours instead of 10? Yes include the estimate of the overtime of the week.
I already got the overtime sorted out. By using this formula =SUM((B22)*24) * B27, i put this formula in B7 which calculates the overtime per hr.
Does the regular work hours and overtime look correct in the calculations?
I didn't touch your formulas in G and H. 17:30 - 7:30 does equal 10 hours. How are you figuring 11?I was looking at G15 & H16, and seems like its possibly off by a hour? Shouldn't C15 & D15 be basically 11 hours instead of 10?
Okay, that's different. So if, only 3 days have been worked so far at 10 hours a day, do you want B22 to show 6 hours or 10 hours (extrapolating out)?Yes include the estimate of the overtime of the week.
This is how I would change it.
Col G adds hours until they hit 40, then excess goes to Col H
In G15 copied down
=IF(SUM($G$14:G14)+(D15-C15)> "40:00"+0, MIN(D15-C15,"40:00"-SUM($G$14:G14)), D15-C15)
In H15 copied down
=IF(SUM($G$15:G15)="40:00"+0, D15-C15-G15,0)
These formulas work if people are working more than a 5 day week and they will sum up correctly at the bottom. Then there's no need for the formula in B22 except as a check.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks