Pivot-table invoice issue

Dear All,

I'm having some trouble with a work spreadsheet (attached). I'm pretty sure there's a way of creating a specific type pivot-table from the first tab which would make manual entry/manipulation of data on the second tab of the sheet redundant.

Currently, I enter operative, job number, and working hours on the first tab. There are different rates in operation up to 37.5 hours and above 37.5 hours, I have a table which works out what rate needs to be applied and total invoice price etc. on the first tab, however, this is not the issue. The client requires a submission with two tabs, the second tab needs to include two near duplicate tables to the first one, however, normal hours are split into one table and overtime hours are split into another table. If there is a job that is spread over both conditions i.e. starts at 36 hours and ends at 42 hours, it would be split into 1.5 hours in the normal column and 4.5 hours in the overtime column.

Currently I do this all manually, I enter all the data into tab 1, then copy it into tab 2, and split it down into either table. I'm pretty sure that this can all be automated but can't work out how to manipulate the pivot-table to do this.

It would be much appreciated if someone could let me know if this is definitely possible, and if it's not too cheeky, if you could maybe give me a few pointers on how to do it too!

Any feedback is welcome,

Thanks,
Niall