Forum Discussion
I am creating a timesheet for the week. Need to learn how to set a value in a cell.
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?
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?
9 Replies
- Hi, I have already replied with an example excel file. Can you please look into that? Let me know if you have any questions.
Hi,
I have already sent you with an example file. Can you please look into that? for your reference resending again.
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)
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:
=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.
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.
