formula to calculate varying hourly rate during one shift

Hi,

Basically I am working on a timesheet where the person has basic contracted hrs of work.{17:30-20:45) Now if he works outside of those hrs he receives overtime {M20}. If he starts work after 17:30 he will still get paid from 17:30.

I have put together a formula but I don't know if it is correct, in fact I think it's wrong {although it still works} it is not giving me exactly what I want.
I would like a formula lets use cell P20, that calculates hrs worked before 20:00hrs bearing in mind that if the start time is after 17:30 they will still be paid from 17:30, but must also include start times before 17:30. {I created a "hidden" column {G} with 20:00 in the cell as I could not figure out any other way to do it. But then 20:00 is automatically in cell P20 until hrs worked are inserted then the figure changes, where I would prefer it to be blank to begin with. Is there a simple way of doing it?

I would also like the figure to be in a number format instead of hh.mm, so 2.5 instead of 2:30. In my formula I have had to multiply 2:30 by 24 to get to the correct total amount.


I have attached a sample of the sheet I am working on...

I have had no training in formulae so I am trying to use logic and work them out myself.. to my own detriment I reckon. But at least I am trying.

Thanks
Princessd

Thank you that works well.. I opting for the formula that omits column G. but in doing that P20 now shows #VALUE although I have amended the formula in column P why does it show #VALUE?

Regards
Princessd