1

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.

enter image description here

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.

Improve this question
2
  • 1
    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).
    – Hennes
    Feb 25, 2018 at 10:59
  • 1
    @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.
    – fixer1234
    Feb 28, 2018 at 22:59

2 Answers 2

Reset to default
2

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)

Solution

Improve this answer
2
  • 1
    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"
    – Máté Juhász
    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).
    – Hennes
    Feb 26, 2018 at 10:41
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.