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. 


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)

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


In AD8:




Fill down to AD21. In AH8:




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!



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.


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.


You mixed up the two SUMs in the formula in C57. It should be




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.