Microsoft Excel - How do I make a double formula in Excel 2007?

Asked By Kayte Barnhill on 11-Feb-13 03:51 AM

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?
Pete Bradshaw replied to Kayte Barnhill on 11-Feb-13 03:59 AM
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
Kayte Barnhill replied to Pete Bradshaw on 12-Feb-13 02:55 AM
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.
Pete Bradshaw replied to Kayte Barnhill on 12-Feb-13 09:59 AM
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