# Place amount if 45h or less, 0 for the overtime, and 45 when more than 45h

I have a small sheet that I would like to use for my employees hours.

So what I want is a formula that will do the following.

I want it to work out that if I have 45hours or less it will place that amount in the hours cell and place "0" in the overtime cell, but if it is more than 45hours, to place "45" in the hours cell and what is left, the difference, in the overtime sell. 45 being the hours per week before overtime is calculated.

I know it is probably some simple formula but my brain is stuck on this one.

At the bottom is the hours cell and overtime cell. Please note that the left side is days of the week, it is just in another language. Monday to Sunday.

• Or in excel speech: `=if( SUM(E2:E8)<=45, 0, SUM(E2:E8)-45 )`. (Note that this does not adjust for weekend hours counting as 150% or 200%, but neither does just sum in cell E7). Feb 25, 2018 at 10:59
• @Hennes, in your answer, you converted all of col E to numbers reflecting units of hours. The OP has time values in col E, so the formula in your comment above won't work. Feb 28, 2018 at 22:59

Logic:

• if sum of time <= 45 then 0 else sum of time - 45
(Sum of time is already in the field D9, so lets use that.)

• if(condition, what if true, what if false)
(Standard excel function)

Which yields: `=IF(E9<=45, 0, E9-45)`

• half good, but OP need to check criteria also for hours: "but if it is more than 45hours, to place "45" in the hours cell and what is left, the difference, in the overtime sell" Feb 26, 2018 at 9:13
• Then we need to replace E9 again with `=SUM(E2:E8)` on both instances of the formula in E10. And E9 would get `=min( 45, SUM(E2:E8) )`. (Untested, but simple enough formulas). Feb 26, 2018 at 10:41

You could also use:

``````=MAX(0,E9-45)
``````

If the number in cell `E9` is less than or equal to 45, `E9-45` will return a negative number, hence `MAX` will return 0; else `MAX` will return the difference.