SOLVED

I am creating a timesheet for the week. Need to learn how to set a value in a cell.

New Contributor

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?

9 Replies

@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. 

@Steveoreno 

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)

best response confirmed by Steveoreno (New Contributor)
Solution

@Hans Vogelaar 

Hey 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?

@Steveoreno 

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.

You are the MAN! Thank you so much for teaching me how to do that. I'm not the most experienced in Excel but, i thought this could be done. Thanks again!

Hi,

 

I have already sent you with an example file. Can you please look into that? for your reference resending again.

Hi, I have already replied with an example excel file. Can you please look into that? Let me know if you have any questions.

@Steveoreno 

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.

@Steveoreno 

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.