Forum Discussion
I am creating a timesheet for the week. Need to learn how to set a value in a cell.
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?
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.
- 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!
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)+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.