Mar 02 2021 06:56 AM
I want to add up the regular hours for the week and if they work more than 40 hours, i want 40 to appear in the regular totals box and anything above 40 in the OT cell box. How do i do this?
Mar 02 2021 07:29 AM
@Steveoreno Hi, I have used some dummy data and formulas to calculate the Regular Hours and OT hours. Please check if this helps to you.
Mar 02 2021 07:47 AM
Let's say the total number of hours is in cell D10.
Use the following formula for the number of regular hours: = MIN(D10,40)
And for the number of overtime hours: =MAX(D10-40,0)
Mar 02 2021 08:25 AM
SolutionHey Hans,
Thanks for the reply. I have attached my spreadsheet to let you see how i have it set up. What i want is to have the sheet add up all of the regular hours per each staffs timecards. If the value for regular hours is over 40, i want the regular hours cell to show only 40 and the extra hours shown in the OT cell. Can this be done?
Mar 02 2021 08:38 AM
In AD8:
=MIN(SUM(B8,F8,J8,N8,R8,V8,Z8),40)
Fill down to AD21. In AH8:
=SUM(E8,I8,M8,Q8,U8,Y8,AC8)+MAX(SUM(B8,F8,J8,N8,R8,V8,Z8)-40,0)
Fill down to AH21.
Mar 02 2021 08:46 AM
Mar 02 2021 09:05 AM
Hi,
I have already sent you with an example file. Can you please look into that? for your reference resending again.
Mar 02 2021 09:07 AM
Mar 02 2021 09:09 AM
Can you tell me what i did wrong in the same example on the 2 sided tab sheet? Same type of excel I just rotated the information so i can get all of the data on one sheet (Front and back) for the payroll officer. The 2 sided tab is the one that i am working on now.
Mar 02 2021 12:09 PM
You mixed up the two SUMs in the formula in C57. It should be
SUM(C8,C15,C22,C29,C36,C43,C50)+MAX(SUM(C4,C11,C18,C25,C32,C39,C46)-40,0)
SUM(C8,C15,C22,C29,C36,C43,C50) is the sum of the overtime hours.
MAX(SUM(C4,C11,C18,C25,C32,C39,C46)-40,0) is the sum of the regular hours over 40.
Mar 02 2021 08:25 AM
SolutionHey Hans,
Thanks for the reply. I have attached my spreadsheet to let you see how i have it set up. What i want is to have the sheet add up all of the regular hours per each staffs timecards. If the value for regular hours is over 40, i want the regular hours cell to show only 40 and the extra hours shown in the OT cell. Can this be done?