I am trying to create a formula in Excel to calculate Overtime. My base formula is: =IF(I5<D41,I5,D41) I also need to add, if the answer to that formula is >8, then put 8 in the cell, otherwise use the answer to the first formula.

How do I do this?Hi Kayte,

You could add another If statement in your original formula, it's called nesting.

Try something like this

=IF(I5<D41,I5,IF(I5>8,D41,I5))

Does this work for you?

Pete

You could add another If statement in your original formula, it's called nesting.

Try something like this

=IF(I5<D41,I5,IF(I5>8,D41,I5))

Does this work for you?

Pete

No, it didn't work. I decided to go another route and make a cell for the first formula so I can do the second formula in the cell where I want it. The idea was to calculate overtime on the weekends where overtime kicks in when you are working more than 8 hours per day, but you also have to calculate when it is more than 40 hours per week. D41 is where I calculate the weekly overtime, I5 is where I calculate the hours I worked that day.

Hi Kayte,

Glad you managed to solve your problem.

Calculating time is fairly straight forward. You need 5 cells, start time, break start, break end and home time.

Each of these cells should be formatted to a time format.

Then you do something like this (make sure this cell is also formatted to a time format).

=Sum(break start - start time) + sum(break end - home time).

Does this work for you?

Pete

Glad you managed to solve your problem.

Calculating time is fairly straight forward. You need 5 cells, start time, break start, break end and home time.

Each of these cells should be formatted to a time format.

Then you do something like this (make sure this cell is also formatted to a time format).

=Sum(break start - start time) + sum(break end - home time).

Does this work for you?

Pete