Calculating Hours worked and Time in Liu owed.

Hi Guys,

Currently have a spreadsheet that i made that will calculate the hours worked when a user enters start and finish time.

e.g

C9(Start Time): 09:00
E9(End Time): 18:30
F9(Hours Paid): 9.00 ((IF(AND(ISNUMBER(C11),ISNUMBER(E11)),MOD(E11-C11,1)*24))-D9)
I9(TIL Earnt): 2.25
J9(TIL Accured):2.25
H9(TIL Used): 0



Now to calculate how many hours is owed to the user:, i take the result above, minus standard working day hours (7.5), that gives me 1.5 hours owed.
It then needs to be multiplied by weekday penalty rate(1.5) or weekend penalty rate(2.0) (*'OT Rates'!$B$2))


C9(Start Time): 09:00
E9(End Time): 18:30
F9(Hours Paid): 9.00 (=(IF(AND(ISNUMBER(C11),ISNUMBER(E11)),MOD(E11-C11,1)*24))-D9)
I9(TIL Earnt): 2.25 (=(((IF(AND(ISNUMBER(C9),ISNUMBER(E9)),MOD(E9-C9,1)*24))-D9)-'OT Rates'!$B$5)*'OT Rates'!$B$2)
J9(TIL Accured):2.25
H9(TIL Used): 0

I assume there would be an easier way to do this envolving dates but am completely unsure on how to go about it

where 'OT Rates' is another worksheet which contains details on standard day and penalty rates.



Everything works fine if hours worked are greater or even then a standard working day(7.5 hours),

E.G

C9(Start Time): 09:00
E9(End Time): 18:30
F9(Hours Paid): 9.00
I9(TIL Earnt): 2.25
J9(TIL Accured):2.25
H9(TIL Used): 0

however if they work say 6 hours, it does the same calculation.

E.G

C9(Start Time): 12:00
E9(End Time): 18:30
F9(Hours Paid): 6.00
I9(TIL Earnt): -2.25
J9(TIL Accured):0
H9(TIL Used): 0

What idealy should happen:

E.G

C9(Start Time): 12:00
E9(End Time): 18:30
F9(Hours Paid): 6.00
I9(TIL Earnt): -1.00
J9(TIL Accured):1.25
H9(TIL Used): 1.00

sample timesheet if the above confuses you:
sampleTimesheet.xlsx

cheers,

daniel