Overtime and payment at half hour intervals with a lower payment for the first half hour

for instance M5:
=L5-9/24
or may be better:
=MAX(0,L5-9/24)
and format as hh:mm
and N5:
=MAX(0,5*ROUNDUP(M5*48,0)-2.5)
and format as currency
5 in formula above is your every (excep first) 30 min overtime payment and 2.5 is 5GBP-first_30_minutes_PAYMENT (5-2.5)

Hi BjarneHansen,
I do not think it is good formula.
Try for instance:

HTML Code: 

But you introduced a constant (5 GBP) in G2, so let's change your formula to:
=IF(E4>1,((E4-1)*$G$2)+0.5*$G$2,0.5*$G$2)
to make use of the constant in G2

and then correct for above mentioned error:
=IF(E4>0,(E4-0.5)*$G$2,0)
(as a side effect we got a shorter formula here, but remember it relies on a "helper cell" E4)

Hi Brett,

This is the problem of representing fractions in excel - they are a double precision floating point numbers.
So if you have for instance 8:00 - 17:00 then the difference is 9:00
And if you deduct 9/24 from it (as in M5) it is displayed as 0:00. So far it looks good.
But if you try a formula
=M5>0
you will get TRUE as a result !!!
moreover - if you change formatting of the cell from time to general, you will see that the value is
5,55112E-17
Pretty small, but then it is multiplied (still very small, but non-zero) and then rounded up - here problem occurs.

If your input data has 1 minute accuracy it will be enough to make a minor correction (half a minute) for N5:
=MAX(0,5*ROUNDUP(M5*48-1/60,0)-2.5)

see attachment.