Excel Formula IF, SUM, ETC...need help

[Recoil83]

Hey all,


I'm working with Excel 2007 and inside a time sheet template and trying to add some additional features. I already used the VSB record macro to clear the time cells and created a button for the macro, easy enough.

I'm now trying to create a new formula that looks at a specific cell(total hours worked) and determines if that number is greater than 40, subtract and show the difference in that cell. If the number is less than or equal to 40, leave the number at 0. Ultimately, I'm trying to separate my OT hours into another cell.

The formula's used to calc the days into the total hours worked are as follows(Wednesday being the beginning of the week).

WEDNESDAY - =IF(OR(ISTEXT(C10),ISTEXT(C13)),"Error in C12 or C15",(C10+C13))
THURSDAY - =IF(OR(ISTEXT(E10),ISTEXT(E13)),"Error in C12 or C15",(E10+E13))
FRIDAY - =IF(OR(ISTEXT(G10),ISTEXT(G13)),"Error in C12 or C15",(G10+G13))
SATURDAY - =IF(OR(ISTEXT(I10),ISTEXT(I13)),"Error in C12 or C15",(I10+I13))
SUNDAY - =IF(OR(ISTEXT(K10),ISTEXT(K13)),"Error in C12 or C15",(K10+K13))
MONDAY - =IF(OR(ISTEXT(M10),ISTEXT(M13)),"Error in C12 or C15",(M10+M13))
TUESDAY - =IF(OR(ISTEXT(O10),ISTEXT(O13)),"Error in C12 or C15",(O10+O13))
TOTAL HOURS - =SUM(B14: O14)
OT HOURS - ???

Total hours is located in cell O15, OT hours is located in cell O16.

Any help with this would be great, I'm sure it will be rather simple for you excel wizards. Thanks in advance.

 

[jprahman]

=IF(O15>40, 015-40,0) is what you need.

BTW, all the info you need to find this stuff is in excel. Just start typing in a command or function and a drop down list will appear containing a list of similarly name functions. Once you type the full function name and opening parenthesis is typed excel will give you a tool tip describing what each parameter does. Also, you can go to Help -> Function Reference for more information.