1

I have been fiddling with this equation for a while now and can't figure out where the mistake is.

Can anyone help?

I am using Excel in Denmark and it requires that I use commas instead of periods as a decimal. Don't ask me why, that's just how their number system works.

=IF((J5-I5)*24-K5>7,5;(J5-I5)*24-K5-L5),IF((J5-I5)*24-K5-L5>2;2;0)

edit: We are calculating hours worked. For example if the start time at 06:00 and finish at 17:00 with a 0.5 hour break, the hours I bill my client will be 10.5. BUT everything worked over 7.5 hours is charged extra.

  • First 7.5 hours is 203.82kr
  • First 2 hours after the 7.5 hours is 253.82kr
  • Every hour after those 2 hours are 303.85kr

I need the cell with the above mentioned formula to calculate the hours worked based on the times entered in J5 and I5 then subtract the break entered in K5. If the result is over 7.5 hours then it should only show the hours over 7.5 (worked 10.5 hours but only says 2). The extra hour should be calculated in a different cell but will pretty much be the same formula.

I hope this makes sense.

Here is a screenshot of the spreadsheet I am using.

screenshot

Hi fixer1234,

So let me start with the bullet points. I just hope I understand your questions properly.

  • I multiplied by 24 so I can subtract the start time from the end time to give me how many hours worked. This way, I simply have to enter what time they clocked in/out and the spreadsheet will figure out the rest.
  • No. No one ever works over night.
  • Yes. K5 and L5 are the hours actually worked rather than the times they worked.
  • I want L5 to display how many hours they worked up to 7.5. M5 to display how many hours over 7.5 hours with a max of 2 hours, and N5 to show how many hours they worked over the 2 hours in M5.

In a nutshell the results should look like this:

Screenshot

  • I5 being clock in time
  • J5 being clock out time
  • K5 being break in hours
  • L5 being total hours worked (max 7.5 hours; if worked less than 7.5 hours - ex. 6 hours, then it should say 6
  • M5 being hour many hours worked over 7.5 (if they worked more than 7.5 hours but max 2)
  • N5 being all remaining hours worked over the 2 hours past 7.5 hours
New contributor
Michael Norris is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 4
    Try this =IF((J5-I5)*24-K5>7,5;(J5-I5)*24-K5-L5;IF((J5-I5)*24-K5-L5>2;2;0)) – jcbermu Aug 12 at 12:54
  • Would you please include som mockup sample data with expected outcome? It's hard to advice on a proper formula without sample data to check what we suggest actually would fit your need. – JvdV Aug 12 at 13:20
  • The formula in jcbermu's comment fixes the syntax errors that will give you Excel warnings. However, the logic in the formula may give you unexpected results. (J5-I5)*24-K5>7,5 could include (J5-I5)*24-K5-L5>2. So you may get to the first TRUE result and stop for cases where you want the 2nd IF test to come into play. If you edit the question to include the range of values that could be in these cells, and the logic you want to apply with your formula, people may be able to help you get a reliable formula. – fixer1234 Aug 13 at 3:43
  • edited question. hope it makes sense – Michael Norris Aug 13 at 10:32
0

You appear to have L5 handled. If you're using IF tests to get it, you may be able to shorten it with a formula like:

  =MIN(7,5;(J5-I5)*24-K5))

That calculates work hours and caps them at 7.5 for normal workhours.

It looks like your formula in the question is for M5 (it returns the 2 hr cap if > 2 hr, but not the value if within 2 hr).

Your formula references L5 in the overtime calculations, but since that can be less than 7.5, you use another IF test to limit it. You're really only interested in 7.5, so you can eliminate most of that by just using 7.5 in the overtime cells. You could also do this without an IF test, using a shorter formula.

The first-2-hours cell, M5, could look like this:

  =MIN(2;MAX(0;(J5-I5)*24-K5-7,5))

The last part of the formula is the same as your first IF test, it calculates total "overtime". But if the person works less than 7.5 hours, the calculation will produce a negative. MAX of 0 or that result returns a zero if the time is less than 7.5 hours, or the overtime hours if greater than 7.5. MIN of 2 or that result gives you the actual overtime if less than 2, or caps it at 2 if greater.

The >2hr cell, N5, could look like this:

  =MAX(0;(J5-I5)*24-K5-9,5)  

For this cell you want any hours greater than 9.5 (the 7.5 base hours plus the first 2 hours of overtime. Again, MAX of 0 or that result ensures that numbers less than 9.5 hours are excluded.

  • Edited question for update – Michael Norris 2 days ago
  • @MichaelNorris, thanks for clarifying the question. Since you covered the details there, I'll remove that part of the question and adjust the rest to reflect your explanation. – fixer1234 2 days ago

Your Answer

Michael Norris is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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