Calculate Overtime hours from daily hours

Hello fellow excelers,

I was hoping someone maybe able to provide a bit of advice on a formula to calculate overtime as part of a large set of hours.

Essentially I've got a number of employees in Column A. In the subsequent columns I have every working day from the beginning of the year. In Column CA I have normal time rates, Column CB Over time rates.

So I need one column to sum all 9.5hrs or less, and the next column to sum anything above 9.5hrs but not including the 9.5hrs if possible.

I've tried SUMIF formulas to extract hours above 9.5 but I don't seem to be having any joy. I think I'm having an excel mental block.

Thanks for any help.

Luke

Ratcat,

That's fantastic, sometime I miss the simplest of functions, and the OT works perfectly.

However your normal time calculation doesn't pick up the first 9.5hrs if they work more than 9.5hrs in a day. I can't think of a way to make a SUMIF or COUNTIF work.
This would be easy if it was a simple IF statement because it would be =IF(A7<=9.5,A7,9.5)

Thanks for your thoughts.