Schedule

[nikb_mt]

So I am creating my own sheet to keep track of my hours worked. I need a formula so that I can have my OT hours separated out. So basically a formula that will populate a number in a cell for anything over 40 hours.

 

[BrandonWLH]

=IF(CellwithHours>40,CellWithHours-40,"")

 

[yogeshmaney]

=SUMPRODUCT(--(yourCellRange>=40),YourCellRange)

 

[nikb_mt]

=IF(I9>40,I9-40,"") is what I just did, and I just get an error in the formula. The SUMPRODUCT formula just copied the information in Cell I9.

 

[nikb_mt]

Basically the following cells have the total hours worked for the week: I9, I13, I17, I21, C25. I need a formula that will calculate anything that's over 40 in those cells.

Thanks

 

[tc88]

=IF(I9>40,I9-40,"") is what I just did, and I just get an error in the formula. The SUMPRODUCT formula just copied the information in Cell I9.

Excel Workbook
	A	B	C
1	Week	Hours	OT
2	1	37
3	2	40
4	3	39
5	4	42	2
6	5	43	3
7	6	48	8
8	7	41	1
9	8	32
Sheet1

Excel 2010

Cell Formulas
Range	Formula
C2	=IF(B2>40,B2-40,"")
C3	=IF(B3>40,B3-40,"")
C4	=IF(B4>40,B4-40,"")
C5	=IF(B5>40,B5-40,"")
C6	=IF(B6>40,B6-40,"")
C7	=IF(B7>40,B7-40,"")
C8	=IF(B8>40,B8-40,"")
C9	=IF(B9>40,B9-40,"")

I just tried this and it works fine for me, I just made up some information.

 

[BrandonWLH]

=IF(I9>40,I9-40,"") is what I just did, and I just get an error in the formula. The SUMPRODUCT formula just copied the information in Cell I9.

=IF(yourcell>40,yourcell-40,"") works fine. Just tested it. Just dont put it into the cell with the hours. Put is anywhere else.

 

[nikb_mt]

I just tried this =IF(I9>40,I9-40,""), and the cell displays nothing at all. I have to use office calc. I don't know if that matters. I have to be doing something wrong here.

 

[nikb_mt]

I got it to work. so if I want it to run that formula on multiple cells what would the formula be?

 

[BrandonWLH]

if I9 was less than/equal to 40 it will show nothing at all. if you want it to show a 0, then change the "" to a 0.

Just copy and paste it in the same general location as your have your I9. If you I9 is location in I10, then if you want to check say I13, you paste in I14 and it will change the reference to match. or is you put the =IF in J9 for I9, then put it in J13, for I13, etc. if you move the paste 4 over, it will move the cell references 4 over as well.