Spreadsheet: 547540d1510606037-timesheet-help-please-timesheet
Sheet: Sheet1

 | Week Commencing |  | 2017-11-11 00:00:00
Day | Date | Rest Day | Duty | Route | Start Time | Finish | Total Time on Duty |  | Break 40 mins | Duty Pays Hours Payable | Amount Due
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Hi Guys - I know you chaps can sort this one out, I have been trying this for a few hours and can't sort it out I KNOW you guys will take up the challenge and get it sorted in about 20 minutes THANKS IN ADVANCE for anyone's HELP!!    -I'm a  BUS [NAME] --    I need to know the total time on duty - hours payable after a 40 min break (every duty) -  I think I have the Start & finish times set up everyday I need to take a 40 minute break - so total time minus the 40 mins ( I guess I don't need a column for the 40mins). Columns for day date duty route not important but Rest day indicated with the (Y) is as I get paid a different amount I also get a higher rate for Sat and Sundays I also get a few extra pence for unsocial hours which are any time after 22:00hrs and 05:00 most of my duties WILL be 15: 00 to about 01:00 the next day - I know there is a lot in there with lots of different payments PLEASE play around edit as you see fit any help is MUCH appreciated..... My formulas are not working here I have included the hourly rates on the sheet it might be handy to use within the formula - so when I get a pay rise I can edit there. THANK YOU AGAIN
Saturday | 2017-11-11 00:00:00 | N | (4)001 | 100 | 16:40:00 | 01:30:00 |  | 40
Sunday | 2017-11-12 00:00:00 | N | (7)002 | 100 | 15:00:00 | 23:30:00 |  | 40
Monday | 2017-11-13 00:00:00 | N | (1)003 | 100 | 12:00:00 | 18:40:00 |  | 40
Tuesday | 2017-11-14 00:00:00 | Y | (1)004 | 100 | 09:00:00 | 18:40:00 |  | 40
Wednesday | 2017-11-15 00:00:00 | N | (1)005 | 100 | 15:01:00 | 00:20:00 |  | 40
Thursday | 2017-11-16 00:00:00 | N | (1)006 | 100 | 14:35:00 | 23:35:00 |  | 40
Friday | 2017-11-17 00:00:00 | N | (1)007 | 100 | 13:00:00 | 23:40:00 |  | 40
[NAME] | 2017-11-13 00:00:00
 |  |  |  |  |  |  |  |  |  | Total (Gross)
 | Week Commencing |  | 2017-11-18 00:00:00
Day | Date | Rest Day | Duty | Route | Start Time | Finish | Total Time on Duty |  | Break 40 mins | Duty Pays Hours Payable | Amount Due
Saturday | 2017-11-18 00:00:00 | N | (4)920 | 201 | 16:17:00 | 01:08:00 |  | 40
Sunday | 2017-11-19 00:00:00 | N | (7)515 | 400 | 16:18:00 | 01:35:00 |  | 40
Monday | 2017-11-20 00:00:00 | N | (1)003 | 100 | 16:00:00 | 17:00:00 |  | 40
Tuesday | 2017-11-21 00:00:00 | N | (1)004 | 100 | 11:00:00 | 18:40:00 |  | 40
Wednesday | 2017-11-22 00:00:00 | OFF | OFF | OFF
Thursday | 2017-11-23 00:00:00 | OFF | OFF | OFF
Friday | 2017-11-24 00:00:00 | N | (1)007 | 100 | 13:00:00 | 23:40:00 |  | 40
 |  |  |  |  |  |  |  |  |  | Total (Gross)
 |  |  |  |  |  |  | Hourly Rates
 |  |  |  |  |  |  | Hourly rate Monday-Friday |  |  | 12.31
 |  |  |  |  |  |  | Overtime rate Monday-Friday |  |  | 13.37
 |  |  |  |  |  |  | Rest Day rate Monday-Friday |  |  | 13.37
 |  |  |  |  |  |  | Hourly Weekend rate |  |  | 13.9
 |  |  |  |  |  |  | Hourly Overtime Weekend rate |  |  | 14.22
 |  |  |  |  |  |  | Hourly Rest Day Weekend rate |  |  | 14.22
 |  |  |  |  |  |  | Unsociable Hours (2200 – 0500) |  |  | 0.35