Adding numbers to cells if over a certain number

[On the grind]

Ok I am trying to write a spreadsheet that records overtime hours. I have each day as a cell (A1, B1, etc) and I would put the hours worked that day into the cell. After the cells go over 40 hours, I want the overtime to be displayed. They would be displayed in cell below it (A2, B2, etc ... so one cell for total hours worked and the cell below is the accumulated overtime hours for the week, if worked over 40 hours for the week).

I hope I explained it good enough. Is there a way to write a formula for this?

Thanks for any help!

 

[Aladin Akyurek]

Ok I am trying to write a spreadsheet that records overtime hours. I have each day as a cell (A1, B1, etc) and I would put the hours worked that day into the cell. After the cells go over 40 hours, I want the overtime to be displayed. They would be displayed in cell below it (A2, B2, etc ... so one cell for total hours worked and the cell below is the accumulated overtime hours for the week, if worked over 40 hours for the week).

I hope I explained it good enough. Is there a way to write a formula for this?

Thanks for any help!

Would you post the data just for 3 days along with the results?

 

[Jonmo1]

Here's the basic syntax for accumulated overtime..

=MAX(0, Sum Of Hours Worked - OT Threashold)

So if your Hours worked are A1:G1, and 40 hours is your threshold for Overtime, it would be

=MAX(0,SUM(A1:G1)-40)

Hope that helps.

 

[On the grind]

Worked perfectly. Thanks!

 

[On the grind]

Actually, that is not what I really wanted (although you gave me exactly what I asked for)...

I want to add the hours over 40 in a cell, but just the hours that would be overtime for that day.

So I have the cells for Sunday thru Saturday. A1 thru G1. Below it are the overtime cells, A2 thru G2. In the A1 thru G1 cells, I put the hours worked for that day. If the hours worked total over 40 hours for the week, then I want the number of hours that are over time just for that day to be displayed on the cells below it. For example, if by the end of the day Wednesday I have 42 hours, the Cell below would have a "2" in it. If Thursday I work 9 hours, then the cell below has "9" in it, not "11" (I don't want them added).

I hope I explained this good enough.

Thanks for any help!

 

[Jonmo1]

In A2
=MAX(0,A1-40)

In B2 and filled right
=IF(A2=0,MAX(0,SUM($A1:B1)-40),B1)

 

[On the grind]

I got some clarification on what is needed and I had it wrong.

I see that this formula adds up the hours if over 40 hours puts that amount over 40 hours in the over-time cell and accumulated total. Although it would make sense to do it this way, that's not how I'm supposed to fill out the spreadsheet.

What they want displayed in the over-time cells is how much over-time for THAT DAY ONLY, IF over 8 hours. So I work 8.5 hours, put .5 hours in the overtime cell. So I assume the formula would be easier??

Thanks again for any help.

- Paul

 

[Jonmo1]

So if you worked 8.5 hours Mon Tue Wed and Thu, but only say 4 on Fri...
That would only be 36 total hours for the week,
But still 2 hours OT because of 30 minutes over 8 on mon - thu?

Try in A2 and filled right

=MAX(0,A1-8)

 

[On the grind]

Yep, that's what I needed...thanks!