Excel calculating conditional overtime with IFS-CodePudding

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

CodePudding user response：

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