Calculate late penality

Hi,
I thought I had my formula working, but after further testing is doesn't.

I am trying to calculate a late shift penality in cell AL7. Basically if an employee works between 19 and midnight then for any hours which fall into this period should be paid $1.65 for every hour worked.
Start time is in cell M7, finish time is in cell N7, total hours worked is in cell P7. The $1.65 is in cell AL5.

The formula I have so far, which I can't seem to get to work is =IF(AND(M7<19,N7>19),((N7-19))*$AL$5,IF(AND(M7>=19,N7<=24),Z7*$AL$5,0))

Can anyone help solve this please.

Cheers

Thank you, but it doesn't work...

Pl see the attached file.

Hi there,

Thank you for your help and I see that your solution works, however I can't have it in time values.

Hi FDibbins,

I looked at the solution that KV has provided. I see that it works when it has been converted to time, but I can not have it in time format, as we use this sheet to upload into our payroll system which can't accept time formats. so if an employee works 7 hours 30 minutes it needs to be 7.5 or 7.25 of it was 7 hours 15 minutes and so on

Hey all,

Thank all you for your suggestions, but just as with my formula as soon as an employee works past midnight, the formula doesn't calculate. so if the start time is 19, but the finish is 1am, the formula doesn't calculate anything, which is the same problem I have with my original formula

Try This In AL7 and drag down.

Please Login or Register  to view this content.

Thank you all for your assistance here. It is greatly appreciated with all the great suggestions we have managed to get it to work.