0

I'm having some issues with calculating overtime. I'm a willing novice with formulae :')

Half day = 5:00 hours
Overtime = up to 7:45hrs
Full Day = 10:00 hours

However anything over 7:45hrs constitutes a full day.

So I need a conditional formula:

if value is between 5:00 and 7:44 - 5:00 = overtime
if value is >10:00 - 10:00 = overtime

I'll give you my novice example of how I've written it

=IFS(and(G9>E15,G9<F15),SUM(G9-time(5,0,0)),"-",AND(G9<E16),SUM(G9-time(10,0,0)),"-")

E15=05:00:00
F15=07:45:00
E16=10:00:00
G9=hours worked

I'm sure I'm making a silly mistake, but any help would be much appreciated!

Chris

Chris Jenkins
  • 3
  • 3
  • What is the expected output? You may want to look at Index/Match, something like `==INDEX({"Half day","Full Day","Overtime","Too much time"},MATCH(J13,{0,2084,0.3126,0.4167},1))` maybe? – cybernetic.nomad Feb 25 '22 at 21:09
  • Why ifs()? Why not if(G9>=E16,full-calc,if(G9>=F15,ot-calc,if(G9>=E15,hrs-calc))) but you can check for errors. – Solar Mike Feb 25 '22 at 21:13
  • I need to know the amount of time worked over 5hrs (half day) but only if it's within the range of 5hrs:7hrs45mins. `=IF(AND(G9>=E15,G9<=F15),G9-time(5,0,0),"-")` This formula works perfectly. However, if total time worked is between 7hrs 45mins and 10hrs no overtime is needed. After 10hrs, the overtime needs to be calculated again. So I try to end the rest of the formula: `=IF(AND(G9>=E15,G9<=F15),G9-time(5,0,0),"-", IF(G9>E16,G9-time(10,0,0),"-"))` but I get this error: Wrong number of arguments to IF. Expected between 2 and 3 arguments, but received 4 arguments. – Chris Jenkins Feb 25 '22 at 22:36

1 Answers1

0

Chris,

In your question you're showing the time worked as a time value, e.g.7:44. If you're really calculating based on time values you'll have to use the time calculation functions to accomplish this not normal math.

That said if you're using quarter hours this formula will work

=IF($A2>10,$A2-10,IF($A2>7.44,0,IF($A2>5,$A2-5,0)))

[![enter image description here][1]][1]

Note the formula goes in B2 and is written to be dragable. [1]: https://i.stack.imgur.com/rNTWv.jpg

RetiredGeek
  • 2,980
  • 1
  • 7
  • 21
  • Thanks @retiredgeek, you haven't directly answered my question but you made me realise that I was putting the IF expressions in the wrong order. I was starting with 5hrs rather than 10hrs. – Chris Jenkins Feb 27 '22 at 13:07